All Forums Database
Brie 4 posts Joined 03/14
21 Mar 2014
Help with creating a Case within an In statment within a Case

Hello!  I need some assistance with writing a case statement that is a bit out of my depth. 
 
Say I have an existing column called Food_Type with the below entries:
Green Apple
Red Apple
Orange
Banana
Hot Dog
Hamburger
Ketchup
Mustard
 
And i want to group them into categories like this case statement:
Case
When Food_Type In ('Green Apple',"Red Apple','Orange','Banana') Then 'Fruit'
When Food_Type In ('Hot Dog',"Hamburger') Then 'Meat'
When Food_Type In ('Ketchup','Mustard') Then 'Condiments'
Else 'other'
End as 'Grocery Type'
 
I then decide I dont care what color apple im dealing with and want to take the entries 'Green Apple' and 'Red Apple' and have them both just say 'Apple' but still appear in my results under the 'Fruit' category. 
 
So, I tried this:
Case
When Food_Type In ((Case when Food_Type In ('Green Apple',"Red Apple') Then 'Apple' End),'Orange','Banana') Then 'Fruit'
When Food_Type In ('Hot Dog',"Hamburger') Then 'Meat'
When Food_Type In ('Ketchup','Mustard') Then 'Condiments'
Else 'other
End as 'Grocery Type'
 
It did not like that. :(  I've spent some time looking here and some other sites, and can not seem to find the answer I need, probably because I am not really sure what it would be called.  Is there a way to accomplish this?  Thanks in advance!

 
 
 

Fred 1096 posts Joined 08/04
24 Mar 2014

 Based on your statement of the intent:
Case
When (CASE WHEN Food_Type IN ('Green Apple','Red Apple') Then 'Apple' ELSE Food_type END)
  In ('Apple','Orange','Banana') Then 'Fruit'
When Food_Type In ('Hot Dog',"Hamburger') Then 'Meat'
When Food_Type In ('Ketchup','Mustard') Then 'Condiments'
Else 'other'
End as "Grocery Type"
 

Brie 4 posts Joined 03/14
26 Mar 2014

It worked!! You're a genius :D (and I was closer than I thought I was!) :)

ToddAWalter 316 posts Joined 10/11
26 Mar 2014

If this is a short list, then doing it in a case expression is fine, but I have often seen the case expression get way out of hand with hundreds or thousands of things to match. If your case is headed in that direction, it would be much easier to build, more performant and much easier to maintain if you put the matching in a table and do a join. Then no changes have to be made to the query, just add, change, delete records in the category table.

Brie 4 posts Joined 03/14
26 Mar 2014

Actually I may have jumped the gun a little...   the query ran, but did not change how the results appeared.  I am still seeing "Green Apple" and "Red Apple" in the category of Fruit, instead of just "Apple" (sticking with my example above). 
That is probably a better plan Todd, but the categories change fairly regularly, and I think I only have view access to the tables.

ToddAWalter 316 posts Joined 10/11
26 Mar 2014

You need a separate Case on food type to display that differently. The two level case above only changes it inside that expression.
Do you have permission to make a table of your own? Frequent changes make the join version more desirable.

Brie 4 posts Joined 03/14
26 Mar 2014

I am working on getting the ability to do this, thanks a bunch for your advice!

You must sign in to leave a comment.