SQL Server CASE .. WHEN .. IN statement

Faisal picture Faisal · May 18, 2011 · Viewed 136.4k times · Source

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.

Answer

Martin Smith picture Martin Smith · May 18, 2011

CASE AlarmEventTransactions.DeviceID should just be CASE.

You are mixing the 2 forms of the CASE expression.