I am using following query:
Select
S.MDSE_ITEM_I,
S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from
eefe_lstr4.SLS_TBL as S
left outer join
eefe_lstr4.PRS_TBL P
on S.MDSE_ITEM_I = P.MDSE_ITEM_I
and S.CO_LOC_I = P.CO_LOC_I
and S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D
left outer join
eefe_lstr4.OROW_RCPT RC
on RC.MDSE_ITEM_I =S.MDSE_ITEM_I
and RC.CO_LOC_I = S.CO_LOC_I
left outer join
eefe_lstr4.OH H
on H.MDSE_ITEM_I =S.MDSE_ITEM_I
and H.CO_LOC_I = S.CO_LOC_I
group by
S.MDSE_ITEM_I,
S.CO_LOC_I;
I am getting error saying:
FAILED: SemanticException Line 0:-1 Both left and right aliases
encountered in JOIN ‘PRSMN_VAL_END_D’
Search shows that this error comes when you have inequality clause in query. However I am not using any inequality clause (<= or >= in my query (just = and between) even then I am getting this error.
I have this valid T-SQL query:
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date
I tried to re-write it for Hive (v0.13) as:
create table tmp_leads2 as
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
from tmp_leads t1
left join param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ;
But I get the error:
Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'
I see the fields it’s talking about, but I’m not sure how to re-write this while keeping the query results identical.
Answer by Paislee Moody
Search shows that this error comes when you have inequality clause in query. However I am not using any inequality clause (<= or >= in my query (just = and between) even then I am getting this error.,Try to move the inequality condition from on clause to the where condition .,I am using following query:,
1
Hi @Unnikrishnan_R any specific reason why the inequality don’t work on join and works on where clause ?
– JKC
Jul 3 ’18 at 4:34
Try to move the inequality condition from on clause to the where condition .
Select S.MDSE_ITEM_I,S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from eefe_lstr4.SLS_TBL as S
left outer join eefe_lstr4.PRS_TBL P on S.MDSE_ITEM_I = P.MDSE_ITEM_I and S.CO_LOC_I = P.CO_LOC_I
left outer join eefe_lstr4.OROW_RCPT RC on RC.MDSE_ITEM_I =S.MDSE_ITEM_I and RC.CO_LOC_I = S.CO_LOC_I
left outer join eefe_lstr4.OH H on H.MDSE_ITEM_I =S.MDSE_ITEM_I and H.CO_LOC_I = S.CO_LOC_I
where(S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D)
group by S.MDSE_ITEM_I, S.CO_LOC_I;
Answer by Madden Mathis
The problem comes from the inequality conditions in the joins. This poses a problem. The following is probably sufficient for your purposes:,I see the fields it’s talking about, but I’m not sure how to re-write this while keeping the query results identical.,Here is something which would not cause inner join or alias issue and give you the expected results in Hive,If you add any files,it will delete all existing files related to this answer-(only this answer)
I have this valid T-SQL query:
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date
I tried to re-write it for Hive (v0.13) as:
create table tmp_leads2 as
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
from tmp_leads t1
left join param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ;
But I get the error:
Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'
Answer by Beckett Bauer
I am using following query:,FAILED: SemanticException Line 0:-1 Both left and right aliases
encountered in JOIN ‘PRSMN_VAL_END_D’,Try to move the inequality condition from on clause to the where condition .,Search shows that this error comes when you have inequality clause in query. However I am not using any inequality clause (<= or >= in my query (just = and between) even then I am getting this error.
I am using following query:
Select
S.MDSE_ITEM_I,
S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from
eefe_lstr4.SLS_TBL as S
left outer join
eefe_lstr4.PRS_TBL P
on S.MDSE_ITEM_I = P.MDSE_ITEM_I
and S.CO_LOC_I = P.CO_LOC_I
and S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D
left outer join
eefe_lstr4.OROW_RCPT RC
on RC.MDSE_ITEM_I =S.MDSE_ITEM_I
and RC.CO_LOC_I = S.CO_LOC_I
left outer join
eefe_lstr4.OH H
on H.MDSE_ITEM_I =S.MDSE_ITEM_I
and H.CO_LOC_I = S.CO_LOC_I
group by
S.MDSE_ITEM_I,
S.CO_LOC_I;
Answer by Ember Rush
Try to move the inequality condition from on clause to the where condition .,you’re right. The where clause should include nulls where records could get dropped:,The problem I see with this approach is that, because there is a left outer join, that means we want to have all registers from left table just once, if we move the conditions to where clause, then those registeres where right table columns are null are lost.
Try to move the inequality condition from on clause to the where condition .
Select S.MDSE_ITEM_I,S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from eefe_lstr4.SLS_TBL as S
left outer join eefe_lstr4.PRS_TBL P on S.MDSE_ITEM_I = P.MDSE_ITEM_I and S.CO_LOC_I = P.CO_LOC_I
left outer join eefe_lstr4.OROW_RCPT RC on RC.MDSE_ITEM_I =S.MDSE_ITEM_I and RC.CO_LOC_I = S.CO_LOC_I
left outer join eefe_lstr4.OH H on H.MDSE_ITEM_I =S.MDSE_ITEM_I and H.CO_LOC_I = S.CO_LOC_I
where(S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D)
group by S.MDSE_ITEM_I, S.CO_LOC_I;
Answer by Veronica Beard
Error when executing hive statement:,Hive Statement Error During Execution: Error while processing statement: FAILED: Execution Error, return code 2 from o,Vue-element-admin Use npm install Error: npm ERR! Error while executing:,ORA-24338 statement handle not executed [How to Solve]
Error when executing hive statement:
Both left and right aliases encountered in JOIN
Cause of error:
Added inequality condition to join's on condition
Just put the inequality condition in where
Answer by Daniella Beil
Search shows that this error comes when you have inequality clause in query. However I am not using any inequality clause (<= or >= in my query (just = and between) even then I am getting this error.,Try to move the inequality condition from on clause to the where condition .,The problem comes from the inequality conditions in the joins. This poses a problem. The following is probably sufficient for your purposes:,This looks for any non-digit character. You can expand this, if your numeric values might have decimal points or commas.
I am using following query:
Select
S.MDSE_ITEM_I,
S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from
eefe_lstr4.SLS_TBL as S
left outer join
eefe_lstr4.PRS_TBL P
on S.MDSE_ITEM_I = P.MDSE_ITEM_I
and S.CO_LOC_I = P.CO_LOC_I
and S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D
left outer join
eefe_lstr4.OROW_RCPT RC
on RC.MDSE_ITEM_I =S.MDSE_ITEM_I
and RC.CO_LOC_I = S.CO_LOC_I
left outer join
eefe_lstr4.OH H
on H.MDSE_ITEM_I =S.MDSE_ITEM_I
and H.CO_LOC_I = S.CO_LOC_I
group by
S.MDSE_ITEM_I,
S.CO_LOC_I;
HIVE: Both Left and Right Aliases Encountered in Join
Recently was stuck on this error while trying to do a JOIN in HIVE. Below is my SQL
create table ma.internet_v2 as
select A.msisdn, A.imsi, A.dt,
A.start_time, A.end_time, A.url, A.ttl_connection_dur_ms,
A.ttl_upload_bytes, A.ttl_download_bytes, A.ttl_cdr_cnt,
coalesce(B.domain_desc,A.domain_desc) domain_desc,
coalesce(B.subdomain_desc, A.subdomain_desc) subdomain_desc
from
db.internet A
left outer join
hfz_domain_name_mapping B
on instr(A.url, B.url_pattern) > 0;
HIVE then give me and error message «Both Left and Right Aliases Encountered in Join».
After going through some mail threads it seems that HIVE can support certain types of join.
Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.
A poster from Stack Overflow suggested that one should use WHERE instead of ON. Duly following his advice, it seemed to do the trick. Final script is as below.
create table ma.internet_v2 as select A.msisdn, A.imsi, A.dt, A.start_time, A.end_time, A.url, A.ttl_connection_dur_ms, A.ttl_upload_bytes, A.ttl_download_bytes, A.ttl_cdr_cnt, coalesce(B.domain_desc,A.domain_desc) domain_desc, coalesce(B.subdomain_desc, A.subdomain_desc) subdomain_desc from db.internet A left outer join hfz_domain_name_mapping B where instr(A.url, B.url_pattern) > 0;
And now to wait for my map reduce to finish. Time to get some coffee 
Popular posts from this blog
How To Reset User Rights in the Default Domain Group Policy in Windows Server 2003
This article describes how to reset user rights in the default domain Group Policy object (GPO) in Windows Server 2003. The default domain GPO contains many default user-rights settings. Sometimes, if you change the default settings, unexpected restrictions may be put on user rights. If the changes are unexpected or if the changes were not recorded so that you do not know which changes were made, you may have to reset the user-rights settings to their default values. This situation may also occur if you manually rebuild the contents of the Sysvol folder, or if you restore it from a backup by using the steps that are included in the following Microsoft Knowledge Base article: http://support.microsoft.com/kb/253268/EN-US/
Hindsight: 8 months down the analytics road
Well this is probably not my typical piece of how-tos or tutorial on analytic tool or that sort of thing. This time it’s just some random piece of thoughts that I thought I’d share with all, having walked the path for nearly a year now since the beginning of 2015. I started this journey following a change in the direction of our company. We’ve decided sometime late last year that big data and analytic had a huge potential in the future of the telecommunication industry, and thus — we should try and revamp our company and adopt this data-driven culture into our daily routine. Personally I thought it was fun. With all the hype of big data and it’s possibilities, not to mention data scientist being a sexy job , I was ecstatic (to say the least) to be part of the bandwagon. Ahem. Not to mention that we also had Hadoop in our IT ecosystem. Seemed like the best place to learn and practice big data analytics — I said to myself. 8 months down the road though, I fe
I have this valid T-SQL query:
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date
I tried to re-write it for Hive (v0.13) as:
create table tmp_leads2 as
select t1.*
,case when s1.period is not null then 'Y' else 'N' end as flag_cur
,case when s2.period is not null then 'Y' else 'N' end as flag_prev
,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur
,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev
from tmp_leads t1
left join param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ;
But I get the error:
Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'
I see the fields it’s talking about, but I’m not sure how to re-write this while keeping the query results identical.
The problem comes from the inequality conditions in the joins. This poses a problem. The following is probably sufficient for your purposes:
create table tmp_leads2 as
select t1.*,
(case when s1.period is not null then 'Y' else 'N' end) as flag_cur,
(case when s2.period is not null then 'Y' else 'N' end) as flag_prev,
s1.cutoff_date as cutoff_date_cur, s1.cutoff_dtkey as cutoff_dtkey_cur ,
s2.cutoff_date as cutoff_date_prev, s2.cutoff_dtkey as cutoff_dtkey_prev
from tmp_leads t1 left join
param s1
on s1.period = '(a) Current' left join
param s2
on s2.period = '(b) Previous'
where (s1.begin_date is null or s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date) or
(s2.begin_date is null or s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date);
This is not exactly equivalent. It makes the assumption that if a parameter is in the table, then it is in the table for all dates. That might be a reasonable assumption. If not, then a more complex query would be needed.
In this case, if we look at the <intent> of the query it is possible that it can be rewritten to not need a non-equi-join.
If you can make this Assumption about the Date Dimension:
A Date Value in the date dimension (such as ‘Foll_Monday’ in the query) will always be between the Week_Start and the Week_End values.
Then you should be able to rewrite the query to not use a non-equi join. The second table in the join would become:
(SELECT
S.Calendar_DateCalendar_Date
,S.Foll_MondayFoll_Monday
,W.Week_Id, Snapshot_Id
FROM
(
SELECT calendar_date,
(
CASE day_of_week
WHEN 1 THEN calendar_date +1
WHEN 3 THEN calendar_date +6
WHEN 4 THEN calendar_date +5
WHEN 5 THEN calendar_date +4
WHEN 6 THEN calendar_date +3
WHEN 7 THEN calendar_date +2
ELSE calendar_date
END
) Foll_Monday
FROM td_etl.calendar) S
inner join (
select <NaturalDateValue>, Week_Id, week_Start, Week_End from td_etl.dim_cal ) W
on S.foll_monday = W.<NaturalDateValue>
Give that a try.
Try to move the inequality condition from on clause to the where condition .
Select S.MDSE_ITEM_I,S.CO_LOC_I,
MAX(S.SLS_D) as MAX_SLS_D,
MIN(S.SLS_D) as MIN_SLS_D,
sum(S.SLS_UNIT_Q) as SLS_UNIT_Q,
MIN(PRSMN_VAL_STRT_D) as PRSMN_VAL_STRT_D,
MIN(PRSMN_VAL_END_D) as PRSMN_VAL_END_D,
MIN(RC.FRST_RCPT_D) as FRST_RCPT_D,
MIN(RC.CURR_ACTV_FRST_OH_D) as CURR_ACTV_FRST_OH_D,
MIN(H.GREG_D) as OH_GREG_D
from eefe_lstr4.SLS_TBL as S
left outer join eefe_lstr4.PRS_TBL P on S.MDSE_ITEM_I = P.MDSE_ITEM_I and S.CO_LOC_I = P.CO_LOC_I
left outer join eefe_lstr4.OROW_RCPT RC on RC.MDSE_ITEM_I =S.MDSE_ITEM_I and RC.CO_LOC_I = S.CO_LOC_I
left outer join eefe_lstr4.OH H on H.MDSE_ITEM_I =S.MDSE_ITEM_I and H.CO_LOC_I = S.CO_LOC_I
where(S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D)
group by S.MDSE_ITEM_I, S.CO_LOC_I;
The problem I see with this approach is that, because there is a left outer join, that means we want to have all registers from left table just once, if we move the conditions to where clause, then those registeres where right table columns are null are lost.
you’re right. The where clause should include nulls where records could get dropped:
where (PRSMN_VAL_STRT_D IS NULL) or (S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D)