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));
