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
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)'
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 'T04' as T04
, (CASE WHEN t3.REEL_TS IS NULL then NULL
(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
) as NEXT_TESTED_ON
from #tmptbl_Work_03g t3