Question

CASE sintaxis error in SELECT

CASE statement, SELECT, 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

You can use OR cases in Query just like:

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 )
)


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