IIF Statement Equivalent - 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 2005 Forums

Transact-SQL (2005)

IIF Statement Equivalent

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-02-22 : 10:52:45

Hello,

I'm creating a view in SQL and am looking for something equivalent to the IIF ststement in Access.

Basically I want to create 2 new columns, which are based on the result of 1 of the other columns.

So if in my columns I have :

ID - NAME - FLAG - TIMEFROM - TIMETO
11 - Fred - 0 - 09:00 - 10:30
12 - John - 1 - 11:30 - 15:30
etc, etc, etc

I want to add the first new column saying something along the lines of "If Flag = 0 then DateTo - DateFrom, otherwise Null"

and then add a second column saying "If Flag = 1 then DateTo - DateFrom, otherwise Null"

Therefore the above would show :

ID - NAME - FLAG - TIMEFROM - TIMETO - NEWCOL1 - NEWCOL2
11 - Fred - 0 - 10:00 - 10:30 - 0:30 - Null
12 - John - 1 - 11:30 - 15:30 - Null - 4:00
etc, etc, etc

This in turn would give me 2 new columns which would show value 1 and value 2 - 1 being where the flag is not checked, and 2 being where the flag is checked.

Does this make sense ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-22 : 11:03:09

Make use of CASE expression.

Case when <condition> then <expression> else <expression> end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-02-22 : 11:16:25

Can I do this in my view ?
The reason I ask is that when I type in the following :

Case when Flag=0 then TimeTo-TimeFrom else 'Null' end

I get a message saying "The Query Designer does not support the CASE SQL construct"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-22 : 11:19:35

Are you using SQL Server?

You need to use Query Analyzer to write the query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 11:20:33

quote:
Originally posted by Jonny1409

Can I do this in my view ?
The reason I ask is that when I type in the following :

Case when Flag=0 then TimeTo-TimeFrom else 'Null' end

I get a message saying "The Query Designer does not support the CASE SQL construct"

Which query designer are you using?It definitely works in SSMS

your view will be like this

CREATE VIEW vwYourView
AS

SELECT ID,NAME,FLAG,TIMEFROM,TIMETO,
Case when Flag=0 then TimeTo-TimeFrom else Null end AS NEWCOL1,
Case when Flag=1 then TimeTo-TimeFrom else Null end AS NEWCOL2
FROM Tables...

GO

please note you dont need quotes for NULL ('NULL' is not equal to NULL)

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-02-22 : 11:23:18

I'm using SQL Enterprise Manager and creating the view in there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 11:25:28