IIf function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IIf function

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-25 : 17:05:24

Hi,

I am not really familiar with IIf function and here is the query that sent to me from person who generated the query from access and now I need to modify this to make it work in sql but I don't know how, please help me with this

Thanks alot

IIf([OfficeType]="50",[OHA].[OHARO], IIf([OfficeType]="11",[AREA],[ROCD],
IIf(NULL([COLOCOFC].[OWNR_OCD]="",[OTHER].[DCORO],[COLOCOFC].[OWNR_OCD])))
as ReportsToOfficeCode

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-25 : 18:57:03

Hi

I think the CASE statement will help.

Something like


SELECT
 Case OfficeType
 WHEN 50 THEN OHARO
 WHEN 11 THEN ROCD
 ELSE OWNR_OCD END as ReportsToOfficeCode

Look up CASE in books online for some more info.

Damian

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-26 : 04:50:46

Just to clarify IIF

IIF (expression1,parameter2,parameter3) returns parameter2 if expression1 is true, parameter3 if not. Read IF expression1 THEN parameter2 ELSE parameter3.

It is common to nest IIFs, replacing parameter3 with a nested IIF:-

IIF (expression1,parameter1, 
	IIF (expression2,parameter2,
		IIF (expression3,parameter3,parameter4)))

Read as:-

IF expression1 THEN parameter1
ELSE IF expression2 THEN parameter2
ELSE IF expression3 THEN parameter3
ELSE parameter 4

Or the equivalent CASE:-

CASE
WHEN expression1 THEN parameter1
WHEN expression2 THEN parameter2
WHEN expression3 THEN parameter3
ELSE parameter4
END

If you have lots to translate you could write a stored procedure to translate them!

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

khenry
Starting Member

16 Posts

Posted - 2002-03-26 : 05:42:21

It stands for Inline If and is used a lot in M$ Access.

Unlike an IF or CASE statement IIF evaluates both result expressions (an IF only evaluates the appropriate result)

You are supposed to avoid them like the plague in Access

K e i t h H e n r y

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources