Optimize case when to use IN? 1

[Qik3Coder]

I have a status column that contains text.
I have to return either everything, or where the status is in(X) based on whether or not a flag was sent

and (i.status = case when @ActiveOnly = 1 then 'OPEN' else i.status END
or
i.status = case when @ActiveOnly = 1 then 'PEND' else i.status END)

is there a way to mix the case when and an IN statement?

-Sometimes the answer to your question is the hack that works

 

[AlexCuse]

From the looks of it I don't think you need CASE at all. From the looks of it you are just joining on i.status = i.status, I don't think this is what you want?

try something like

and ((@ActiveOnly = 1 AND i.status in ('PEND', 'END')) OR @ActiveOnly <> 1)

(not tested)

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[Qik3Coder]

Thanks Alex, with the way things have been going today I think I left my brain at the house...

-Sometimes the answer to your question is the hack that works

 

[AlexCuse]

Don't worry, its' happier there :-D

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century