Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Changing IIF to Case Statements

    I'm not sure if its something am doing...

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

    Code:
    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.

    Code:
    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 :-)

    This thread is taken from one I created in
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110383

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're missing a comma before CASE.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I feel such a dope :-) I been tairing my hair out for last hour or so looking at this :-)

    Thanks...

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is why I format my queries the way I do
    Code:
    SELECT comma
         , separated
         , list
         , of
         , columns
         , CASE WHEN ...
    By putting the comma at the beginning of the line and putting each column on it's own line, I feel it's easier to spot this common mistake.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •