Faisal Faisal - 8 months ago 56
SQL Question

SQL Server CASE .. WHEN .. IN statement

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 Source

CASE AlarmEventTransactions.DeviceID should just be CASE.

You are mixing the 2 forms of the CASE expression.