Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Case statement error in Beeline hive

Highlighted

Case statement error in Beeline hive

Hi

I have a table in Hbase and row key is a combination of three columns

 

Hostname#starttime_type

 

I used split function to take the timestamp from the row key (hbid)

 

Then I applied hours(timestamp)

 

I am getting hour value as 0,1,2,3 etc

 

 

Now I want to group by hours range.

 

If case hour=0 then “print range 0-1”

If case hour=1 then “ print range 0-1”

If case hour=2 then “print range 2-4”

If case hour=3 then “print range 2-4”

 

case when hour(split(split(hbid,"#")[1],"_")[1])==0  

then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2")

 

Select country,case when hour(split(split(hbid,"#")[1],"_")[1])==0  then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==1 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==2 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") when hour(split(split(hbid,"#")[1],"_")[1])==3 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") end,column5 from table name group by case when hour(split(split(hbid,"#")[1],"_")[1])==0  then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==1 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==2 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") when hour(split(split(hbid,"#")[1],"_")[1])==3 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") end,country

 

If I try to print hour(split(split(hbid,"#")[1],"_")[1]) I am getting all the values correctly like 0,1,2,3,4 upto 22.

But when I use case statements its not working.It shows only one value range 8-10 is printed.

 

I think case 0 – case 9 is not read directly goes to case 10 then only prints case 10 not goes to any other cases.

 

Can I know the proper syntax of case statements?

 

Is else statement mandatory in case statements?

 

Other than case any other statements like if else if we can use?