CASE sintaxis error in SELECT

5 pts.
Tags:
CASE statement
SELECT statement
SQL Server 2005
T-SQL
In the middle of a large storage procedure I have a working SELECT 01 (see below) which at a couple of points uses CASE statement. I need to expand the most inner CASE to get SQL Server check for several values instead of one. Currently CASE I am talking about looks like (it is in the middle of SELECT 01) tests.var_id = CASE WHEN t3.PMN1='1' then 392 WHEN t3.PMN1='2' then 514 else 638 end I need it to accept tests.var_id = 392 OR 393 OR 304 for PMN1='1' 514 OR 515 OR 516 for PMN1='2' 638 OR 639 OR 640 for all other values of PMN1 So, I put together following case: tests.var_id IN CASE WHEN t3.PMN1='1' then '(392,393,394)' WHEN t3.PMN1='2' then '(514,515,516)' else '(638,639,640)' end Apparently something is wrong - I am getting error message Incorrect syntax near the keyword 'CASE'. Could someone in the know a) suggest the code which will accomplish what I need and/or b) explain silly me what I am doing wrong (in simple" English for dummies") Your attention and time is very much appreciated. SELECT 01: select 'T04' as T04 , t3.PEN , t3.EVID , t3.EDPU , t3.EDTS , t3.ORIP , t3.ORDER_NUMBER , t3.SHIPMENT_DATE , t3.CusId , t3.CuRepT , t3.SOEVID1 , t3.SOEV_NUM1 , t3.PMN1 , t3.REEL , t3.REEL_ID , t3.REEL_TS , t3.ProdRun , t3.pr_vid , (CASE WHEN t3.REEL_TS IS NULL then NULL else (select top 1 tests.result_on from gbdb.dbo.tests tests where tests.result_on > t3.REEL_TS and (tests.var_id = CASE WHEN t3.PMN1='1' then 392 WHEN t3.PMN1='2' then 514 else 638 end ) and tests.result IS NOT NULL order by tests.result_on ) end ) as NEXT_TESTED_ON into #tmptbl_Work_04 from #tmptbl_Work_03g t3

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can use OR cases in Query just like:

<b>and (
(tests.var_id = CASE WHEN t3.PMN1=’1′ then 392
WHEN t3.PMN1=’2′ then 514 else 638 end )
OR
(tests.var_id = CASE WHEN t3.PMN1=’1′ then 393
WHEN t3.PMN1=’2′ then 515 else 639 end )
OR
(tests.var_id = CASE WHEN t3.PMN1=’1′ then 394
WHEN t3.PMN1=’2′ then 516 else 640 end )
)</b>

this will sort out your problem
If the parameter are more then 3 ie Unknown, you have required to write Dynamic SQL.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following