You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Sanjay Subramanian <sa...@yahoo.com> on 2015/01/09 01:07:02 UTC
Using IF in the JOIN clause

hey guys
This is a portion of a long query we wrote.Can u advise if the bold portion will work ?
    and if(f.fr_name is not null, f.fr_name, e.fr_name)=d.fr_name     and if(f.pos_bin is not null, f.pos_bin, e.pos_bin)=d.pos_bin 
thanksregards

sanjay

PART OF A LARGER QUERY======================from (    select z.* from (    select city, fr_name, (floor((none_position_scaled-1)/10)+1) * 10 as pos_bin,     sum(if(job_count>0,60,if(int_flag=1,6,if(mjp_flag=1,1,0)))) as one_six_sixty_sum,     sum(job_count) as job_sum,     sum(pay_count) as pay_sum,     if(sum(hours_count) is null, 0, sum(hours_count)) as hours_sum,    count(*) as counter    from oletas02.scorek_x_done     group by city, fr_name, (floor((none_position_scaled-1)/10)+1) * 10    order by city, fr_name, pos_bin    ) z     where pos_bin <= 200 and NOT pos_bin is null) ffull outer join (    select z.* from (    select city, fr_name, (floor((none_position-1)/10)+1) * 10 as pos_bin,     sum(if(job_count>0,60,if(int_flag=1,6,if(mjp_flag=1,1,0)))) as one_six_sixty_sum,     sum(job_count) as job_sum,     sum(pay_count) as pay_sum,     if(sum(hours_count) is null, 0, sum(hours_count)) as hours_sum,    count(*) as counter    from oletas02.scorek_x_done     group by city, fr_name, (floor((none_position-1)/10)+1) * 10    order by city, fr_name, pos_bin    ) z     where pos_bin <= 200 and NOT pos_bin is null) e on f.city=e.city and f.fr_name=e.fr_name and f.pos_bin=e.pos_binfull outer join (    select z.* from (    select city, fr_name, (floor((either_position_scaled-1)/10)+1) * 10 as pos_bin,     sum(if(job_count>0,60,if(int_flag=1,6,if(mjp_flag=1,1,0)))) as one_six_sixty_sum,     sum(job_count) as job_sum,     sum(pay_count) as pay_sum,     if(sum(hours_count) is null, 0, sum(hours_count)) as hours_sum,    count(*) as counter    from oletas02.scorek_x_done     group by city, fr_name, (floor((either_position_scaled-1)/10)+1) * 10    order by city, fr_name, pos_bin    ) z     where pos_bin <= 200 and NOT pos_bin is null) d on if(f.city is not null, f.city, e.city)=d.city     and if(f.fr_name is not null, f.fr_name, e.fr_name)=d.fr_name     and if(f.pos_bin is not null, f.pos_bin, e.pos_bin)=d.pos_bin