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
--
[quoted text, click to view] "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
--
[quoted text, click to view] "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:
>
> > 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 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
Hello --CELKO--,
This did not work.
[quoted text, click to view] >>> 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. <<
> 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)
Don't see what you're looking for? Try a search.
