Hi Dudu, Actually we use both fields from left and right tables, I mentioned right table just for my convenience to check whether ALL from right table can be pulled as per join condition match.
One more reason why we use left join is we should not have extra columns after join. Kishore On Tue, Apr 19, 2016 at 5:46 AM, Markovitz, Dudu <dmarkov...@paypal.com> wrote: > Before dealing with the technical aspect, can you please explain what is > the point of using LEFT JOIN without selecting any field from table A? > > > > Thanks > > > > Dudu > > > > *From:* Kishore A [mailto:kishore.atmak...@gmail.com] > *Sent:* Tuesday, April 19, 2016 2:29 PM > *To:* user@hive.apache.org > *Subject:* Question on Implementing CASE in Hive Join > > > > 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 > > >
