Hi Dudu, Thank you for sending queries around this.
I have run these queries and below are the observations 1. It did return the same error as before" SemanticException [Error 10017]: Line 4:4 Both left and right aliases encountered in JOIN 'code'" 2. Query execution is successful but not retrieving any results out of it. I am clueless and not able to proceed to next step until this is resolved. Do you have any other suggestions please? Kishore On Tue, Apr 19, 2016 at 6:08 AM, Markovitz, Dudu <dmarkov...@paypal.com> wrote: > Please try the following two options. > > Option 2 might be better, performance wise (depending of the data volume > and characteristics). > > > > P.s. > > I didn’t understand the explanation about the LEFT JOIN > > > > > > Dudu > > > > 1. > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* = b.*type* > > *and* a.code like *case* b.code *when* > 'ALL' *then* '%' *else* b.code *end* > > *and* a.indicator like *case* b.indicatior *when* > 'ALL' *then* '%' *else* b.indicatior *end* > > *;* > > > > > > > > 2. > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* = b.*type* > > *and* a.code = b.code > > *and* a.indicator = b.indicatior > > > > *where* b.code != 'ALL' > > *and* b.indicatior != 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* = b.*type* > > *and* a.indicator = b.indicatior > > > > *where* b.code = 'ALL' > > *and* b.indicatior != 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* = b.*type* > > *and* a.code = b.code > > > > *where* b.code != 'ALL' > > *and* b.indicatior = 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* = b.*type* > > > > *where* b.code = 'ALL' > > *and* b.indicatior = 'ALL' > > *;* > > > > > > *From:* Kishore A [mailto:kishore.atmak...@gmail.com] > *Sent:* Tuesday, April 19, 2016 3:51 PM > *To:* user@hive.apache.org > *Subject:* Re: Question on Implementing CASE in Hive Join > > > > 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 > > > > >
