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.
3 Answers
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)
