July 18, 2008 at 2:35 pm
In the middle of a large stored procedure I have a working SELECT (see SELECT 01 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
)-- in my code it is a closing parensis, I do not know why make a face out of it in the code
end
) as NEXT_TESTED_ON
into #tmptbl_Work_04
from #tmptbl_Work_03g t3
July 18, 2008 at 2:51 pm
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
Change it to:
(CASE WHEN t3.PMN1 = '1' AND tests.var_id IN (392,393,394) THEN 1
WHEN t3.PMN1 = '2' AND tests.var_id IN (514,515,516) THEN 1
WHEN t3.PMN1 <> '1' AND t3.PMN1 <> '2' AND tests.var_id IN (638,639,640) THEN 1
ELSE 0
END) = 1
* Noel
July 18, 2008 at 3:12 pm
jzurbo77 (7/18/2008)
In the middle of a large stored procedure I have a working SELECT (see SELECT 01 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.
I think you are getting stuck on trying to use CASE for this. It really isn't necessary, although it can be done...
If I understand what you are looking for, it is this:
t3.PMN1='1' AND tests.var_id IN (392,393,394)
Or t3.PMN1='2' AND tests.var_id IN (514,515,516)
Or tests.var_id IN (638,639,640)
If the above is correct, it can be written as:
WHERE ((t3.PMN1 = '1' AND tests.var_id IN (392,393,394))
OR (t3.PMN1 = '2' AND tests.var_id IN (514,515,516))
OR tests.var_id IN (638,639,640))
If you really want to use a CASE expression, it could be written as:
WHERE (CASE WHEN t3.PMN1='1' AND tests.var_id IN (392,393,394) THEN 1
WHEN t3.PMN1='2' AND tests.var_id IN (514,515,516) THEN 1
WHEN tests.var_id IN (638,639,640) THEN 1
ELSE 0
END) = 1
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2008 at 5:54 pm
Thank you very much, both of you. Your solution resolved "Sintax error" problem. As I am trying to make sure entire stored procedure still performs as it should I am facing strange problems - will post them as separate issues as they are not related (I think) to that particular SELECT.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply