Menu
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.
499
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
CASE can be nested in another CASE as well as in another IF…ELSE statement. In addition to SELECT, CASE can be used with another SQL clause like UPDATE, ORDER BY.
Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.
CASE AlarmEventTransactions.DeviceID should just be CASE.
You are mixing the 2 forms of the CASE expression.
68
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With