Conditional join using case statement in 'on' clause in HiveQL

Question

Let's say: the values in the column of tbl_A to join on have different lengths: 5 and 10. The values in the column of tbl_B to join on are larger length and when joining substr() should be applied depending on the length of the values in tble_A. So I was trying to apply a case statement in 'ON' clause when joining the tables using HiveQL, and I get following error:

Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 22:3 Both left and right aliases encountered in JOIN '11'

Here is my code:

select  
a.fullname, b.birthdate
from mydb.tbl_A a
left join mydb.tbl_B b
on a.fullname = 
   case when length(a.fullname) = 5 then substr(b.othername,1,5)
   when length(a.fullname)= 9 then substr(b.othername, 8, 9) end
and a.birthdate = b.birthdate

I could not find much information on this. Your help will be much appreciated. Thank you.

score 0 · Answer 1 · answered May 25 '17 at 18:01

JOIN currently has some limitations.
Here is a work-around.

select  a.fullname
       ,b.birthdate

from                tbl_A a

        left join   tbl_B b

        on          a.fullname  = 
                    substr(b.othername,1,5)

                and a.birthdate = 
                    b.birthdate

where   length(a.fullname) <> 9
     or a.fullname is null

union all

select  a.fullname
       ,b.birthdate

from                tbl_A a

        left join   tbl_B b

        on          a.fullname  = 
                    substr(b.othername,8,9)

                and a.birthdate = 
                    b.birthdate

where   length(a.fullname) = 9

Conditional join using case statement in 'on' clause in HiveQL

1 Answers1