Changing IIF to Case Statements

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)

Changing IIF to Case Statements

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-10 : 10:20:40

I'm strugling looking at changing the IIF Statement I have in Access to a Case Statement, I was looking over the thread below but can't workout fully what I need to do. Can someone explain how I can go about changing over?
[url]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70480
[/url]

My IIF Statment


Weeks Range: IIf([Weeks Waiting]=0,0,IIf([Weeks Waiting]<4,1,IIf([Weeks Waiting]<8,2,IIf([Weeks Waiting]<13,3,99))))

My attempt at a Case Statement


(CASE WHEN [Weeks Waiting] =0 THEN 0 ELSE 99) AS [Weeks Range]

This is the error message

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-10 : 10:50:30

Select *,
case
when [Weeks Waiting] = 0 then 0
when [Weeks Waiting] < 4 then 1
when [Weeks Waiting] < 8 then 2
when [Weeks Waiting] < 13 then 3
else 99
end as WeeksRange
from
MyTable --Table Containing the WeeksWaiting field

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-10 : 10:51:44

The Select statement is missing.

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-11 : 03:26:48

I've tried that Vinnie881 and still get an error message, I'm not sure if its something am doing...

This is the query I have built with the CASE statement included

SELECT DISTINCT
[WaitingListID], [PatientID], [PatientName], [WaitingListName], [WaitingListDescription], [DateonWaitingList],
[ReferralArchiveFlag], [PatientArchiveFlag], WL_TYPE, [ReferredtoTeam], DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7 AS [Weeks Waiting] CASE WHEN [Weeks Waiting] = 0 THEN 0 WHEN [Weeks Waiting] < 4 THEN 1 WHEN [Weeks Waiting] < 8 THEN 2 WHEN [Weeks Waiting]
< 13 THEN 3 ELSE 99 END AS [Weeks Range]
FROM dbo.REP_OP_WAITING_LIST
GROUP BY [WaitingListID], [PatientID], [PatientName], [WaitingListName], [WaitingListDescription], [DateonWaitingList],
[ReferralArchiveFlag], [PatientArchiveFlag], WL_TYPE, [ReferredtoTeam]
HAVING ([DateRemovedfromWaitingList] IS NULL) AND ([WaitingListName] = 'dtc')

This is the error message.

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CASE'.

I also tried the CASE Statement this way, and get the same error message.

SELECT DISTINCT
[WaitingListID], [PatientID], [PatientName], [WaitingListName], [WaitingListDescription], [DateonWaitingList],
[ReferralArchiveFlag], [PatientArchiveFlag], WL_TYPE, [ReferredtoTeam], DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7 AS [Weeks Waiting] CASE WHEN DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7 = 0 THEN 0 WHEN DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7 < 4 THEN 1 WHEN DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7< 8 THEN 2 WHEN DATEDIFF(DAY, [DateonWaitingList], GETDATE())
/ 7< 13 THEN 3 ELSE 99 END AS [Weeks Range]
FROM dbo.REP_OP_WAITING_LIST
GROUP BY [WaitingListID], [PatientID], [PatientName], [WaitingListName], [WaitingListDescription], [DateonWaitingList],
[ReferralArchiveFlag], [PatientArchiveFlag], WL_TYPE, [ReferredtoTeam]
HAVING ([DateRemovedfromWaitingList] IS NULL) AND ([WaitingListName] = 'dtc')

Is it something I'm trying to do that cant be done or have got the wrong end of stick :-)

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-11 : 05:06:12

I have sorted this now, it was me being a muppet and missing a comma off before Case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources