title: Conditional join using case statement in 'on' clause in HiveQL
body: <p>Let's say: the values in the column of <strong>tbl_A</strong> to join on have different lengths: 5 and 10.
  The values in the column of <strong>tbl_B</strong> to join on are larger length and when joining <code>substr()</code> 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:</p>
  
  <blockquote>
    <p>Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 22:3 Both left and right aliases encountered in JOIN '11'</p>
  </blockquote>
  
  <p>Here is my code:</p>
  
  <pre><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
  </code></pre>
  
  <p>I could not find much information on this. Your help will be much appreciated. Thank you. </p>