title: Hive - Case statement in join - error
body: <p>I am facing a problem when using the case statement in a left join in Hive. </p>
  
  <p>Hive query below  - </p>
  
  <pre><code>select 
      m.InventoryId,
      m.dataproviderid,   
      m.dealerid,   
      case when ti.makeid is null then T.MakeId else ti.makeid end,
      ti.makename,
      ti.modelname,
      ti.yearindependentmodelid,
  from InventoryMRout111 m
  join InventoryTrim111 T on (m.InventoryId = T.InventoryId)
  left join Styleinfo TI on ti.configuratorsourceid = 2122 
  and 
  (case 
                            when t.chid is not null and  
  t.chid = ti.chid then 1
                            when t.mdlid is not null and t.mdlid = 
  ti.mdlid then 1
                            when t.makeid is not null and t.makeid = ti.makeid 
  then 1
                            else 0
                            end )                        
  left join NewMinYear111 MY on (1=1)
  where T.Trimsource in ('ranker','Match')
  and m.Status = 'SUCCESS'
  </code></pre>
  
  <p>Error: </p>
  
  <pre><code>FAILED: SemanticException [Error 10017]: Line 20:26 Both left and right aliases encountered in JOIN '0'
  </code></pre>
  
  <p>Any ideas what has to be fixed here ?</p>
  
  <p>I also tried moving the case statement to the where clause..</p>
  
  <pre><code>select 
      m.InventoryId,
      m.dataproviderid,   
      m.dealerid,   
      case when ti.makeid is null then T.MakeId else ti.makeid end,
      ti.makename,
      ti.modelname,
      ti.yearindependentmodelid,
  from InventoryMRout111 m
  join InventoryTrim111 T on (m.InventoryId = T.InventoryId)
  left join Styleinfo TI on ti.configuratorsourceid = 2122                       
  left join NewMinYear111 MY on (1=1)
  where T.Trimsource in ('ranker','Match')
  and m.Status = 'SUCCESS'
  and
  (case 
                            when t.chid is not null and  
  t.chid = ti.chid then 1
                            when t.mdlid is not null and t.mdlid = 
  ti.mdlid then 1
                            when t.makeid is not null and t.makeid = ti.makeid 
  then 1
                            else 0
                            end )  ;
  </code></pre>
  
  <p>But I get this error - </p>
  
  <pre><code>FAILED: ClassCastException org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector cannot be cast to org.apache.hadoop.hive.serde2.objectinspector.primitive.BooleanObjectInspector
  </code></pre>
  
  <p>Any advice on what I am doing wrong here ?</p>