CASE WHEN with "IN" ?

[cbsm]

I am working with Sybase (sorry I do not konw the version, but can't be too old).

This is what I like to do

SELECT * FROM mytable
where myfield in CASE WHEN @Variable = 1 then ('test1')
WHEN @Variable = 2 then ('test2')
WHEN @Variable = 3 then ('test1','test2')
ELSE ('others','test0')

This is not working, because of the [in] instead of the usual [=] .
Do you know a way I could re-write my query to obtain the result I like (or do I have to test my variable before the select and one select per possible case )
Thank you !

 

[mrdenny]

Try something like this.

select *
from mytable
where (@Variable = 1 and myfield = 'Test1')
   or (@Variable = 2 and myfield = 'test2')
   or (@Variable = 3 and myfield in ('test1', 'test2'))

When doing this I can't remember if you need to use or or and, so you may need to try both.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com