14

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.

Kim Moritz
  • 165
  • 1
  • 11
abhiieor
  • 3,132
  • 4
  • 30
  • 47

3 Answers3

24

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;
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • 1
    Hi @Unnikrishnan_R any specific reason why the inequality don't work on join and works on where clause ? – JKC Jul 03 '18 at 04:34
  • 1
    Hi @JKC since we have used left outer join , inequality will not work in the on condition.It will consider all the rows from the table which is in the left side of the join. – Unnikrishnan R Jul 16 '18 at 00:57
15

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.

kkica
  • 4,034
  • 1
  • 20
  • 40
Zoraida
  • 181
  • 1
  • 5
  • 1
    Is there a solution for this problem? – Sanchit Grover Oct 05 '17 at 09:07
  • 1
    Rather than moving the `left join` conditions to the `where` clause, remove them altogether and add an `IF(condition, var, NULL) AS var` to the select statements. But then, in this case, you may end up with more rows than you would get from the original query (if it had worked) -- but at least you won't lose any rows. – Robert Yi May 03 '19 at 22:51
  • @SanchitGrover, is there a solution to this problem? I am struggling with it now. – bernando_vialli Dec 01 '19 at 14:49
  • I solved the problem by using sub-query. Select * from employee a left join (select * from salary_table where salary > 1000) b on a.employee_id = b.employee_id – Sanchit Grover Dec 02 '19 at 09:19
5

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)

Kash
  • 351
  • 6
  • 11