Nested case error: mismatched input 'AS' expecting KW_END near 'end' in case expression : Hive

Question

I am trying to execute this case in Hive

select case when (hour(rand_date)>=0 and hour(rand_date)<6) then 'early_morning' else 
case when (hour(rand_date)>=6 and hour(rand_date)<12) then 'morning' else 
case when (hour(rand_date)>=12 and hour(rand_date)<18) then 'afternoon' else
case when (hour(rand_date)>=18 and hour(rand_date)<24) then 'night' else 'other' end AS hourbins,
 rand_date from mock_ads_dates ;

It gives me an error as

Error while compiling statement: FAILED: ParseException line 4:85 mismatched input 'AS' expecting KW_END near 'end' in case expression

Answer 1

Nested case statements are bit tricky in hive. It should be like this:-

select case when (hour(rand_date)>=0 and hour(rand_date)<6) then 'early_morning' 
when (hour(rand_date)>=6 and hour(rand_date)<12) then 'morning'
when (hour(rand_date)>=12 and hour(rand_date)<18) then 'afternoon'
when (hour(rand_date)>=18 and hour(rand_date)<24) then 'night' else 'other' end AS hourbins,rand_date from mock_ads_dates ;