Nested Case Statements in Hive

Question

Does anyone have an idea on why this code isn't working?

create table2 as
select
    *,
    1 as count,
    case
        when a=1 then
            case 
                when tx="A_L" then "L"
                when tx="B_A" then "A"
                when tx="C_E" then "E"
                when tx in ("E_V","D_M","H_O","I_D") then "Other"
                when tx="F_S" then "S"
                when tx="G_L" then "L"
            end 
        when b=1 then 
            case
                when tx="A_L" then "L"
                when tx="B_A" then "A"
                when tx="C_E" then "E"
            end
        else
            case
                when tx="A_L" then "L"
                when tx="B_A" then "A"
                when tx="C_E" then "E"
                when tx in ("D_M","E_V","F_S","H_O","I_D") then "Other"
                when tx="G_L" then "L"
            end
    end as tx1
from table1

Or is there a simpler way to do this? I'm writing this within a proc sql statement and pushing it to Hadoop (so it needs to be HiveQL compatible).

Solution

Combine nested Case statements into one case statement using either AND or OR clause.This will work.

Comments

[Allan Bowe]

can you share the results from your log? And how do you define 'not working' ?

[Gordon Linoff]

I don't have hive on hand right now, but the count alias is suspicious (it might be a reserved word), single quotes are safer than double quotes, and a table alias with * (table1.*) are worth trying.

[tzhang94]

@RawFocus In SAS and on Hue, the code simply does not run. Does it all look fine to you?

[tzhang94]

@GordonLinoff I'll try putting count in quotes but I think that part is working fine - the nested case statements seem to be the issue because when I greatly simplify them (I need them to be set up in this complex way to return null if a=1, and tx not in any of the ones listed, etc.), the query works