The second version works as expected (after fixing a typo in the word
‘indicator’).
If you don’t get any results you should check your data (maybe the fields
contains trailing spaces or control characters etc.).
If you’re willing to replace the ‘OUTER’ with ‘INNER’, there’s another option -
select *
from b
cross join a
where a.type = b.type
and a.code like case b.code when 'ALL' then '%' else b.code
end
and a.indicator like case b.indicator when 'ALL' then '%' else
b.indicator end
;
Dudu
From: Kishore A [mailto:[email protected]]
Sent: Wednesday, April 20, 2016 5:04 PM
To: [email protected]
Subject: Re: Question on Implementing CASE in Hive Join
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
<[email protected]<mailto:[email protected]>> 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:[email protected]<mailto:[email protected]>]
Sent: Tuesday, April 19, 2016 3:51 PM
To: [email protected]<mailto:[email protected]>
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
<[email protected]<mailto:[email protected]>> 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:[email protected]<mailto:[email protected]>]
Sent: Tuesday, April 19, 2016 2:29 PM
To: [email protected]<mailto:[email protected]>
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
create table a (type int,code string,indicator string);
create table b (type int,code string,indicator string);
insert into table b values
(1,'A','ALL')
,(1,'B','Y')
,(1,'C','X')
,(1,'C','Z')
,(1,'D','Y')
,(1,'D','Z')
,(1,'ALL','X')
,(2,'A','ALL')
,(2,'B','Y')
,(3,'ALL','ALL')
,(4,'ALL','ALL')
;
insert into table a values
(1,'A','X')
,(1,'A','Y')
,(1,'B','X')
,(1,'B','Y')
,(1,'C','Z')
,(2,'A','X')
,(2,'A','Y')
,(2,'A','Z')
,(2,'B','X')
,(3,'A','X')
,(3,'B','Z')
,(3,'C','Y')
;
select *
from b
cross join a
where a.type = b.type
and a.code like case b.code when 'ALL' then '%' else b.code
end
and a.indicator like case b.indicator when 'ALL' then '%' else
b.indicator end
;
1 A ALL 1 A X
1 A ALL 1 A Y
1 ALL X 1 A X
1 ALL X 1 B X
1 B Y 1 B Y
1 C Z 1 C Z
2 A ALL 2 A X
2 A ALL 2 A Y
2 A ALL 2 A Z
3 ALL ALL 3 A X
3 ALL ALL 3 B Z
3 ALL ALL 3 C Y
select *
from b
left join a
on a.type = b.type
and a.code = b.code
and a.indicator = b.indicator
where b.code != 'ALL'
and b.indicator != 'ALL'
union all
select *
from b
left join a
on a.type = b.type
and a.indicator = b.indicator
where b.code = 'ALL'
and b.indicator != 'ALL'
union all
select *
from b
left join a
on a.type = b.type
and a.code = b.code
where b.code != 'ALL'
and b.indicator = 'ALL'
union all
select *
from b
left join a
on a.type = b.type
where b.code = 'ALL'
and b.indicator = 'ALL'
;
1 A ALL 1 A X
1 A ALL 1 A Y
1 ALL X 1 A X
1 ALL X 1 B X
1 B Y 1 B Y
1 C X NULL NULL NULL
1 C Z 1 C Z
1 D Y NULL NULL NULL
1 D Z NULL NULL NULL
2 A ALL 2 A X
2 A ALL 2 A Y
2 A ALL 2 A Z
2 B Y NULL NULL NULL
3 ALL ALL 3 A X
3 ALL ALL 3 B Z
3 ALL ALL 3 C Y
4 ALL ALL NULL NULL NULL
