Hive JOIN中同时遇到左右别名;没有任何不平等条款 [英] Both left and right aliases encountered in Hive JOIN; without any inequality clause
本文介绍了Hive JOIN中同时遇到左右别名;没有任何不平等条款的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下查询:
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;
我在说错误:
失败:SemanticException行0:-1左别名和右别名 在JOIN'PRSMN_VAL_END_D'中遇到
FAILED: SemanticException Line 0:-1 Both left and right aliases encountered in JOIN 'PRSMN_VAL_END_D'
搜索显示,当查询中有不相等子句时,将出现此错误.但是,即使在出现此错误的情况下,我也没有在查询中使用任何不等式子句(<=
或>=
(只是=
和between
).
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.
推荐答案
尝试将不等式条件从on子句移到where条件.
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;
这篇关于Hive JOIN中同时遇到左右别名;没有任何不平等条款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文