Help with creating a Case within an In statment within a Case

Database
N/A

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!

6 REPLIES
Teradata Employee

Re: Help with creating a Case within an In statment within a Case

 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"

N/A

Re: Help with creating a Case within an In statment within a Case

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

Re: Help with creating a Case within an In statment within a Case

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.

N/A

Re: Help with creating a Case within an In statment within a Case

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.

Teradata Employee

Re: Help with creating a Case within an In statment within a Case

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.

N/A

Re: Help with creating a Case within an In statment within a Case

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