Hi, I have a scenario to implement to cases in Hive Joins. I need to implement case on the value on which join condition to be applied.
Table A Code// Type// Indicator// Value// A 1 XYZ John B 1 PQR Smith C 2 XYZ John C 2 PQR Smith D 3 PQR Smith E 3 XYZ Smith F 4 MNO Smith G 3 MNO Smith D 1 XYZ John N 3 STR Smith Table B Code// Type// Indicator// Value// ALL 1 XYZ John D 3 ALL Smith ALL 1 PQR Smith I need to stamp Value from TableB by joining TableA and I am writing join condition as below. Note : No instance of ALL for Type column, a value for Type will be provided. Select b.Code,b.Value from B LEFT JOIN A a ON a.Code = (case when b.Code = 'ALL' then a.Code else b.Code END) AND a.Type = b.Type AND a.Indicator = (case when b.Indicatior = 'ALL' then a.Inidicator else b.Inidicator END) When I run this in hive this query is failing with below error Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 4:0 Both left and right aliases encountered in JOIN 'Code'. Please let me know if more details are needed Thanks, Kishore
