Toad World® Forums

Case Statements in Calculated Column

Hi. I am trying to create a calculated column step in T&C where I can use the result of a case statement as a subquery. I was thinking that I can either set a temp variable or assign it a name as below. Not sure about syntax. The error I keep getting is the missing keyword. Thanks!

The version I am using is 5.1.7.245

CASE WHEN (AID_TYPE='X') THEN
(CASE WHEN (ITEM_TYPE ='1' OR
ITEM_TYPE IN ('120', '220')) THEN '2'
WHEN ITEM_TYPE IN ('120') OR
ITEM_TYPE IN ('240','247') THEN '3'
ELSE '4' END) AS Y
ELSE ''

If you're trying to create a calculated column, then you've already named it, so any alias you're trying to explicitly define in the CASE construct could be conflicting... I would remove the "AS Y" phrase.

Well, I'm trying to do a nested case statement. So, instead of writing three different calculated columns and then each calling on the prior as a result, I just want one column if possible. Is it possible/. Maybe case statements aren't the way to go?

Hi Gary,

I tried taking out AS Y but it still said missing keyword. Any other suggestions? Thanks.

CASE WHEN (AID_TYPE='X') THEN
(CASE WHEN (ITEM_TYPE ='1' OR
ITEM_TYPE IN ('120', '220')) THEN '2'
WHEN ITEM_TYPE IN ('120') OR
ITEM_TYPE IN ('240','247') THEN '3'
ELSE '4' END)
ELSE ''

Might want to verify CASE statement syntax and structure from a good mySQL reference doc online.
However, I just now noticed that you don't have a second END for your parent CASE.

Thanks! Worked!