Using OR or IN() with CASE to specify >1 condition?

Question

Using OR or IN() with CASE to specify >1 condition?

Rich Mechaber

SSChampion

Points: 11130
More actions
September 12, 2008 at 8:35 am

#212569

I cannot seem to get any variant of the following to work, as either a simple or searched CASE. Is it possible?
CASE Lunch
WHEN ('Lasagna' OR 'Sausage') THEN 'High'
WHEN ('Salad' OR 'Non-Fat Yoghurt') THEN 'Low'
END As FatContent
The only attempt I've gotten to work is the annoyingly redundant:
CASE
WHEN (Lunch='Lasagna' OR Lunch='Sausage') THEN 'High'
WHEN (Lunch='Salad' OR Lunch='Non-Fat Yoghurt') THEN 'Low'
END As FatContent
Thanks very much,
Rich Mechaber

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mtassin SSC-Insane Points: 23099 More actions · Answer 1

rmechaber (9/12/2008)
I cannot seem to get any variant of the following to work, as either a simple or searched CASE. Is it possible?
CASE Lunch
WHEN ('Lasagna' OR 'Sausage') THEN 'High'
WHEN ('Salad' OR 'Non-Fat Yoghurt') THEN 'Low'
END As FatContent
The only attempt I've gotten to work is the annoyingly redundant:
CASE
WHEN (Lunch='Lasagna' OR Lunch='Sausage') THEN 'High'
WHEN (Lunch='Salad' OR Lunch='Non-Fat Yoghurt') THEN 'Low'
END As FatContent
Thanks very much,
Rich Mechaber

That's not how CASE works in T-SQL. When you use it, there's two basic choices...

Simple cases can be done as CASE (variable or field ) WHEN (single condition) THEN (thing to do)

Complex cases (like you have) are done as CASE WHEN (complex case) THEN (thing to do)

So try this instead

CASE WHEN Lunch = 'Lasagna' OR Lunch = 'Sausage' THEN 'High'

WHEN Lunch = 'Salad' OR Lunch = 'Non-Fat Yoghurt' THEN 'Low'

END As FatContent

Or alternatively

CASE Lunch

WHEN 'Lasagna' THEN 'High'

WHEN 'Sausage' THEN 'High'

WHEN 'Salad' THEN 'Low'

WHEN 'Non-Fat Yoghurt' THEN 'Low'

END As FatContent

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

CASE

WHEN Lunch IN ('Lasagna','Sausage') THEN 'High'

WHEN Lunch IN ('Salad','Non-Fat Yoghurt') THEN 'Low'

WHEN Lunch IN ('Burger King') THEN 'Really High'

END As FatContent

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

Rich Mechaber

SSChampion

Points: 11130

September 12, 2008 at 9:52 am

#871107

That's it, thanks, Michael!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

You probably should create a table that has the lunch type and the scale, rather than hard-coding the names into a case statement like this.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 5

Thanks, this was a fictitious example. It's just before lunch, and I'm hungry!

Maybe I should head to Burger King?

🙂