Case Statement

I am trying to create a statement that has a case in the where. I have it
working but when I add in a IN... to the case statement it doesn't return
anything.
Sample.
Select * from dbo.Sometable
Where SomeTable.SomeField IN (case @.Variable when 1 then 'sometext1' When
2 then 'sometext2' when 3 then 'sometext1, sometext2')
passing 1 works
passing 2 works
passing 3 nothing returns
Any Ideas?
Thanks,>> I am trying to create a statement that has a case in the where. I have i
t
working but when I add in a IN... to the case statement it doesn't
return
anything. <<
There is no CASE statement in SQL; there is a CASE expression! You are
also confusing columns and fields.
SELECT *
FROM Sometable
WHERE some_col
= (CASE @.variable
WHEN 1 THEN 'sometext1'
WHEN 2 THEN 'sometext2'
WHEN 3 THEN 'sometext1, sometext2'
ELSE NULL END);|||SQL is interpreting 'sometext1, sometext2' as a single string, and not
expanding it to a set the way you would like it to.
Try this instead:
DECLARE @.Variable int
Select * from dbo.Sometable
where
CASE
WHEN SomeTable.SomeField = 'sometext1' THEN 1
WHEN SomeTable.SomeField = 'sometext1' THEN 2
WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
ELSE 0
END = @.Variable
"Richard Thayne" wrote:

> I am trying to create a statement that has a case in the where. I have it
> working but when I add in a IN... to the case statement it doesn't return
> anything.
> Sample.
> Select * from dbo.Sometable
> Where SomeTable.SomeField IN (case @.Variable when 1 then 'sometext1' When
> 2 then 'sometext2' when 3 then 'sometext1, sometext2')
> passing 1 works
> passing 2 works
> passing 3 nothing returns
> Any Ideas?
> Thanks,
>
>|||Oops. What I meant was
DECLARE @.Variable int
Select * from dbo.Sometable
where
CASE
WHEN SomeTable.SomeField = 'sometext1' THEN 1
WHEN SomeTable.SomeField = 'sometext2' THEN 2 --correction made here
WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
ELSE 0
END = @.Variable
"Mark Williams" wrote:
> SQL is interpreting 'sometext1, sometext2' as a single string, and not
> expanding it to a set the way you would like it to.
> Try this instead:
> DECLARE @.Variable int
> Select * from dbo.Sometable
> where
> CASE
> WHEN SomeTable.SomeField = 'sometext1' THEN 1
> WHEN SomeTable.SomeField = 'sometext1' THEN 2
> WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
> ELSE 0
> END = @.Variable
>
> --
> "Richard Thayne" wrote:
>|||Hello --CELKO--,
This did not work.

> working but when I add in a IN... to the case statement it doesn't
> return
> anything. <<
> There is no CASE statement in SQL; there is a CASE expression! You
> are also confusing columns and fields.
> SELECT *
> FROM Sometable
> WHERE some_col
> = (CASE @.variable
> WHEN 1 THEN 'sometext1'
> WHEN 2 THEN 'sometext2'
> WHEN 3 THEN 'sometext1, sometext2'
> ELSE NULL END);