case when statement - 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 2000 Forums

Transact-SQL (2000)

case when statement

Author

Topic

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 02:13:23

Here is another question

I found that case when statement can only use in the where clause where with a '=' or 'like'.

For example:

select state_id
from state
where state_id = case when {option} = 1 then 'CA'
when {option}=2 then 'GA'
end

if I use 'IN' to choose multiple records then case when statement won't work. Is anyone used case when statement with 'IN' selection?

Any input in this ? Thanks for your time!!

Kristen
Test

22859 Posts

Posted - 2006-04-04 : 07:00:46

Can't you use something like


WHERE    ({option} = 1 AND state_id = 'CA')
      OR ({option} = 2 AND state_id = 'GA')

instead?

Kristen

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-04 : 11:46:04

Yes, you can use "IN". How exactly did you want to use it? You can do this, for example:

SELECT state_id FROM state
WHERE state_id =
CASE WHEN option IN (1, 3, 5) THEN 'CA'
WHEN option IN (2, 4, 6) THEN 'GA'
END

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 13:52:08

I am trying to do this:

SELECT state_id FROM state
WHERE state_id IN
CASE WHEN option = 1 THEN ('CA', 'MA','NE',.....)
WHEN option = 2 THEN ('GA', 'DC',.........)
END

Can someone make it to work? Thanks a lot for your help!!

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 23:23:03

Any idea? Suggestions for this? Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 00:21:44

You can use Kristen's method. Any particular reason it is not suitable for your use ?

select	state_id
from	state
where	(	option = 1 and state_id in ('CA', 'MA', 'NE')	)
or	(	option = 2 and state_id in ('GA', 'DC')	        )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:56:38

>>Can someone make it to work?

Did you try what Kris suggested?
Did you get any error?

Madhivanan

Failing to plan is Planning to fail

cr488
Starting Member

23 Posts

Posted - 2006-04-06 : 23:19:22

I will try it tomorrow and to see if it will work. Thanks!

cr488
Starting Member

23 Posts

Posted - 2006-04-12 : 16:54:02

This works great, thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources