Conditional join in Hive

Question

I want to execute below query in Hive -

select * from supp a inner join trd_acct b
on
(a.btch_id = 11170 AND a.btch_id = b.btch_id)
OR (a.btch_id = 11164 AND a.supp_id = b.supp_id)

But getting error -

FAILED: SemanticException [Error 10019]: Line 3:1 OR not supported in JOIN currently 'supp_id'

Answer 1

You can solve this with UNION :

select * from supp a inner join trd_acct b
 on a.btch_id = 11170 AND a.btch_id = b.btch_id
UNION ALL
select * from supp a inner join trd_acct b
 on a.btch_id = 11164 AND a.supp_id = b.supp_id

Or you can try CASE EXPRESSION :

select * from supp a
inner join trd_acct b
 on CASE WHEN a.btch_id = 11164 THEN a.supp_id 
         WHEN a.btch_id = 11170 THEN a.btch_id END
  = CASE WHEN a.btch_id = 11164 THEN b.supp_id
         WHEN a.btch_id = 11170 then b.btch_id END

Answer 2

Below query is working fine in Hive(Hadoop) -

select * from supp a inner join trd_acct b
where
(a.btch_id = 11170 AND a.btch_id = b.btch_id)
OR (a.btch_id = 11164 AND a.supp_id = b.supp_id)

I have tested in Hive Console.