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<mailto: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<mailto:kishore.atmak...@gmail.com>]
Sent: Tuesday, April 19, 2016 2:29 PM
To: user@hive.apache.org<mailto: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
