SQL Server CASE WHEN IN statement

Question

On SQL server 2005 I am trying to query this select statement   SELECT AlarmEventTransactionTableTable TxnID         CASE AlarmEventTransactions DeviceID          WHEN DeviceID IN   7    10    62    58                               60    46    48    50                               137    139    142    143    164               THEN  01           WHEN DeviceID IN   8    9    63    59                               61    47    49    51                               138    140    141    144    165               THEN  02           ELSE  NA         END AS clocking         AlarmEventTransactionTable DateTimeOfTxn FROM   multiMAXTxn dbo AlarmEventTransactionTable    It returns the error below      Msg 156  Level 15  State 1  Line 4   Incorrect syntax near the   keyword  IN     Please give me some advice on what could be wrong with my code

User · Accepted Answer

CASE AlarmEventTransactions DeviceID should just be CASE   You are mixing the 2 forms of the CASE expression

User · Answer

It might be easier to read when written out in longhand using the  simple case  e g    CASE DeviceID     WHEN  7    THEN  01     WHEN  10   THEN  01     WHEN  62   THEN  01     WHEN  58   THEN  01     WHEN  60   THEN  01     WHEN  46   THEN  01     WHEN  48   THEN  01     WHEN  50   THEN  01     WHEN  137  THEN  01     WHEN  139  THEN  01     WHEN  142  THEN  01     WHEN  143  THEN  01     WHEN  164  THEN  01     WHEN  8    THEN  02     WHEN  9    THEN  02     WHEN  63   THEN  02     WHEN  59   THEN  02     WHEN  61   THEN  02     WHEN  47   THEN  02     WHEN  49   THEN  02     WHEN  51   THEN  02     WHEN  138  THEN  02     WHEN  140  THEN  02     WHEN  141  THEN  02     WHEN  144  THEN  02     WHEN  165  THEN  02     ELSE  NA   END AS clocking      which kind makes me thing that perhaps you could benefit from a lookup table to which you can JOIN to eliminate the CASE expression entirely

User · Answer

Thanks for the Answer  I have modified the statements to look like below   SELECT      AlarmEventTransactionTable TxnID       CASE      WHEN DeviceID IN  7    10    62    58    60                46    48    50    137    139                 141    145    164   THEN  01      WHEN DeviceID IN  8    9    63    59    61                47    49    51    138    140                142    146    165   THEN  02               ELSE  NA  END AS clocking       AlarmEventTransactionTable DateTimeOfTxn FROM      multiMAXTxn dbo AlarmEventTransactionTable

User · Answer

Try this     SELECT     AlarmEventTransactionTableTable TxnID      CASE         WHEN DeviceID IN  7    10    62    58    60                     46    48    50    137    139                     142    143    164   THEN  01          WHEN DeviceID IN  8    9    63    59    61                     47    49    51    138    140                     141    144    165   THEN  02          ELSE  NA  END AS clocking      AlarmEventTransactionTable DateTimeOfTxn  FROM     multiMAXTxn dbo AlarmEventTransactionTable   Just remove highlighted string  SELECT     AlarmEventTransactionTableTable TxnID      CASE AlarmEventTransactions DeviceID         WHEN DeviceID IN  7    10    62    58    60

[sql] SQL Server CASE .. WHEN .. IN statement

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to case-when