SQL Server CASE WHEN IN statement

0 votes

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.

Feb 8 in Database by Neha
• 6,260 points
22 views

1 answer to this question.

0 votes

Two forms of CASE statements are getting mixed in your query
CASE AlarmEventTransactions.DeviceID should be written just CASE in the query
It should look like:

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
answered Feb 8 by Vaani
• 5,240 points

Related Questions In Database

0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,360 points
577 views
0 votes
2 answers

Case in Select Statement

I think these could be helpful for ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
46 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
78 views
0 votes
1 answer

SQL Server replaces LEFT JOIN for LEFT OUTER JOIN in view query

You are getting the joins confused and ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
76 views
0 votes
0 answers

Selecting COUNT(*) with DISTINCT

In SQL Server 2005, I have a ...READ MORE

Feb 14 in Database by Neha
• 6,260 points
73 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
286 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 5,240 points
14 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
79 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 5,240 points
39 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7 in Database by Vaani
• 5,240 points
49 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP