Doing an IIF statement in SQL - 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

General SQL Server Forums

New to SQL Server Programming

Doing an IIF statement in SQL

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-21 : 15:55:30

Hi,
I'm having problem trying to do an iif.
This is what total of each month for now.
month total
01 2217.3700
02 2187.6600
03 2243.6500
04 2216.1000
05 2374.4200
06 2296.2200
07 2266.6000
08 2164.2600
09 2085.2400

The months are from 01-12 and 99. I want to combine month 12 and 99 as one.

This is the sql i have, but it comes back in error

SELECT postfiscalMonth,
Object, Title, IIf((postfiscalMonth = ('12'),
total+(select total from dbo.SFY08Staffmonths
where postfiscalmonth = ('99')),IIf(postfiscalMonth = ('24'),
total+(select total from dbo.SFY08Staffmonths
where postfiscalmonth = ('25')), total))) AS Total FROM dbo.SFY08Staffmonths

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 15:58:58

case when postfiscalMonth = '12' then ... else ... end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-21 : 16:40:46

I know i can do a case when, but i was just wondering if i can do an iif instead and what did i do wrong to have that post as an error/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 16:42:11

You can't use IIf in T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-21 : 16:51:40

So the case when is the same thing as the iif then.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 17:19:44

SELECT case when postfiscalMonth = '99' then '12' else postfiscalMonth end ,
sum(total)
from tbl
group by case when postfiscalMonth = '99' then '12' else postfiscalMonth end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 17:22:07

SELECT case when postfiscalMonth = '99' then '12' else postfiscalMonth end ,
sum(total)
from tbl
group by case when postfiscalMonth = '99' then '12' else postfiscalMonth end

Sorry - had a problem with my connection. Re-reading the question this is probably more like what you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-21 : 17:43:49

Thanks. that works great!!!!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources