Both left and right aliases encountered in Hive JO

2020-06-09 07:50发布

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.

标签: sql hive hiveql
3条回答
再贱就再见
2楼-- · 2020-06-09 08:02

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;
查看更多
Summer. ? 凉城
3楼-- · 2020-06-09 08:06

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)

查看更多
smile是对你的礼貌
4楼-- · 2020-06-09 08:13

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.

查看更多
登录 后发表回答