case when in a where statement 1

[sjjustina]

I'm stumped on this one and it seems simple enough but it just isn't working. I've tried using an if statement with no luck either. I don't care which as long as it works.

WHERE ti.TheatreId in
(CASE WHEN @Group = 'Amstar' THEN ('1562', '1564', '1565', '1566', '1567', '1568', '1569', '1570') END)
(CASE WHEN @Group = 'Grand' THEN ('1552', '1553', '1554', '1555', '1556', '1558', '1560', '1563') END)

it gives an syntax error near ',' I've taken out the case statement and ran it with both criteria by cutting and pasting and it's fine. But put in the case statement no go. I'm so frustrated!

Thanks in advance.
Sarah

 

[Crowley16]

that's not how you use a case statement...

Case
when a = b then
when b = c then
else
end

--------------------
Procrastinate Now!

 

[sjjustina]

Thanks, but I've been trying every variation of a case statement including using one case statement like you said and two and neither works??? When I change it like your example I get the same error???

 

[sjjustina]

(CASE
WHEN @Group = 'Amstar' THEN ('1562', '1564', '1565', '1566', '1567', '1568', '1569', '1570')
WHEN @Group = 'Grand' THEN ('1552', '1553', '1554', '1555', '1556', '1558', '1560', '1563')
END)

same error

 

[markros]

Very similar question was asked yesterday on another forum.

http://forums.asp.net/t/1436217.aspx

So, your condition is:

WHERE (@Group = 'Amstar' AND ti.TheatreId in
('1562', '1564', '1565', '1566', '1567', '1568', '1569', '1570')) OR (@Group = 'Grand' AND ti.TheatreId in ('1552', '1553', '1554', '1555', '1556', '1558', '1560', '1563'))

 

[sjjustina]

THANK YOU markros, I've been searching the web for HOURS trying to figure this out.

thank you again!

 

[markros]

You're welcome.