Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Got Invalid table alias or column reference 'NEW_AGE while using CASE statement in hive

avatar
Rising Star

Got Invalid table alias or column reference 'NEW_AGE while using CASE statement in hive. Please find the query below and do the needful, I want to get the result(NEW_AGE) from WHEN condition and again process this results(NEW_AGE) to another CASE statement.

SELECT CONSUMER_R , CNTRY_ISO_C ,

NEW_AGE =(CASE WHEN ( DTBIRTH_Y = '0001-01-01' ) THEN 0 ELSE cast((DATEDIFF(current_date,'0001-01-01')/365) as smallint) END),

CASE

WHEN NEW_AGE = 0 THEN CAST('99) UNKNOWN' as char(11))

WHEN NEW_AGE BETWEEN 1 AND 18 THEN CAST('01) < 18' as char(11))

WHEN NEW_AGE BETWEEN 18 AND 24 THEN CAST('02) 18 ~ 24' as char(11))

WHEN NEW_AGE BETWEEN 25 AND 29 THEN CAST('03) 25 ~ 29' as char(11))

WHEN NEW_AGE BETWEEN 30 AND 34 THEN CAST('04) 30 ~ 34' as char(11))

WHEN NEW_AGE BETWEEN 35 AND 39 THEN CAST('05) 35 ~ 39' as char(11))

WHEN NEW_AGE BETWEEN 40 AND 44 THEN CAST('06) 40 ~ 44' as char(11))

WHEN NEW_AGE BETWEEN 45 AND 49 THEN CAST('07) 45 ~ 49' as char(11))

WHEN NEW_AGE BETWEEN 50 AND 54 THEN CAST('08) 50 ~ 54' as char(11))

WHEN NEW_AGE BETWEEN 55 AND 59 THEN CAST('09) 55 ~ 59' as char(11))

WHEN NEW_AGE BETWEEN 60 AND 64 THEN CAST('10) 60 ~ 64' as char(11))

WHEN NEW_AGE BETWEEN 65 AND 69 THEN CAST('11) 65 ~ 69' as char(11))

WHEN NEW_AGE BETWEEN 70 AND 74 THEN CAST('12) 70 ~ 74' as char(11))

WHEN NEW_AGE >= 75 THEN cast('13) 75 +' as char(11))

ELSE cast('99) UNKNOWN' as char(11))

END NEW_AGE_RANGE, UPDATE_Y, UPDATE_M FROM datamart_db.M_C_CONSMR_TBL WHERE (coalesce(AGE_R, 0) <> coalesce(NEW_AGE, 0));

1 REPLY 1

avatar

@Sundar Lakshmanan

When you are having a derived column then you should create an alias for it instead of treating it like a variable assignment.

Ex. Select (your calculation for derived column) as derived_column.

SELECT CONSUMER_R , CNTRY_ISO_C ,

(CASE WHEN ( DTBIRTH_Y = '0001-01-01' ) THEN 0 ELSE cast((DATEDIFF(current_date,'0001-01-01')/365) as smallint) END) as NEW_AGE,

....