36

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Faisal
  • 533
  • 1
  • 5
  • 9

5 Answers5

46

CASE AlarmEventTransactions.DeviceID should just be CASE.

You are mixing the 2 forms of the CASE expression.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
13

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', ...)

Maxime
  • 8,645
  • 5
  • 50
  • 53
Vinay Kumar
  • 141
  • 3
  • I have fixit as 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, – Faisal May 18 '11 at 12:00
13

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
Faisal
  • 533
  • 1
  • 5
  • 9
4

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

the case sintax it easy:

CASE

WHEN condition THEN result

WHEN condition THEN result

ELSE result END’

please check thi article about sql case https://thedevelopmentstages.com/select-case-sql-como-usar-ejemplos/

Ammiel Arturo Marte
  • 1
  • 1