SQL Hive JOIN 中遇到的左右别名；没有任何不平等条款
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39852325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Both left and right aliases encountered in Hive JOIN; without any inequality clause
提问by abhiieor
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'
失败：SemanticException 行 0:-1 在 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.
搜索显示当查询中有不等式子句时会出现此错误。但是，即使我收到此错误，我也没有使用任何不等式子句（<=或>=在我的查询中（只是=和between）。
回答by Unnikrishnan R
Try to move the inequality condition from on clause to the where condition .
尝试将不等式条件从 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;
回答by Zoraida
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 tablejust once, if we move the conditions to whereclause, then those registeres where right table columnsare nullare lost.
我用这种方法看到的问题是，因为有一个left outer join，这意味着我们希望有来自所有寄存器左表只有一次，如果我们把条件where子句，则那些registeres右表中的列都null将丢失。
回答by Kash
you're right. The where clause should include nulls where records could get dropped:
你是对的。where 子句应包含可能删除记录的空值：
where (PRSMN_VAL_STRT_D IS NULL) or (S.SLS_D between PRSMN_VAL_STRT_D and PRSMN_VAL_END_D)
其中 (PRSMN_VAL_STRT_D IS NULL) 或（PRSMN_VAL_STRT_D 和 PRSMN_VAL_END_D 之间的 S.SLS_D）
