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.
This question is related to
sql
sql-server
sql-server-2005
case-when
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
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.
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', ...)
Source: Stackoverflow.com