Hive JOIN 中遇到的左右别名；没有任何不等式条款

Question

我正在使用以下查询：

 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;

我收到错误消息：

FAILED: SemanticException Line 0:-1 在 JOIN ‘PRSMN_VAL_END_D’ 中遇到左右别名

搜索显示当您在查询中有不等式子句时会出现此错误。 However I am not using any inequality clause ( <= or >= in my query (just = and between ) even then I am getting这个错误。

原文由 abhiieor 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

尝试将不等式条件从 on 子句移至 where 条件。

 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;

原文由 Unnikrishnan R 发布，翻译遵循 CC BY-SA 3.0 许可协议