case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-06 : 06:40:51

Hi,
I have simplified my sql with a sample below...
In the query below I am trying to place an if statement within the where clause.
So basically, depending what you pass in to @Name, then the where clause has a different criteria.
The error I get says: Incorrect syntax near the keyword 'in'.
Do you see what the problem is please?
Thanks

@declare @Name char(3)
declare @SomeValue int

set @Name = 'jo' -- 'MIKE'

select
tAddress.Address
from
table1 inner join tAddress on table1.Field2 = tAddress.Field2
where
table1.ID = @somevalue
and
case
when upper(@Name) = 'JO' then
tAddress.Field3 in ('NY', 'CA', 'Ohio')
end
case
when upper(@Name) = 'MIKE' then
tAddress.Field3 in ('London', 'city')
end

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 06:43:04

[code]
select
tAddress.Address
from
table1 inner join tAddress on table1.Field2 = tAddress.Field2
where
table1.ID = @somevalue
and
case upper(@Name)
when 'JO' then
tAddress.Field3 in ('NY', 'CA', 'Ohio')
when 'MIKE' then
tAddress.Field3 in ('London', 'city')
end

[/code]

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-06 : 06:53:44

Incorrect syntax near the keyword 'in'.

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 07:22:06

Sorry my mistake.Try this


select
tAddress.Address
from
table1 inner join tAddress on table1.Field2 = tAddress.Field2
where
table1.ID = @somevalue
and 
(tAddress.Field3 in ('NY', 'CA', 'Ohio') and 1 =case when upper(@name)='JO' then 1 else 0 end)
OR
(tAddress.Field3 in ('London', 'city') and 1 =case when upper(@name)='MIKE' then 1 else 0 end)

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-06 : 08:28:21

I do not understand your case statements.
Can you please make it like the following for me to uderstand?
where
...
and
case upper(@Name) = 'JO'
when '' then field in ('', '', '')
when '' then field in ('', '', '')

Hope you see what I mean.
Thanks

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 08:33:17

Case statements dont allow IN clause in itself.The syntax wont work the way you want.
Did you try my query & is the o/p as expected?

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-06 : 08:42:36

ok,
The output is correct.
Thank you

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 08:43:47