topic Re: left outer join in Application Development and Automation Discussions

left outer join

Former Member — Mon, 06 Sep 2010 14:07:38 GMT

The following code doesn't work because you can't have two left outer joins in a single slect statement:

select ekko~ebeln ekpo~ebelp lifnr ekpo~matnr ekpo~menge ekpo~meins
          ekbe~vgabe budat ekbe~menge as meng1 wrbtr ekbe~waers
  from   ekko left outer join ekpo on ekko~ebeln = ekpo~ebeln 
                     left outer join ekbe on ekbe~ebeln = ekpo~ebeln
                                                and ekbe~ebelp = ekpo~ebelp 
  into corresponding fields of table i_order
  where ekko~ebeln = p_order
  order by ekpo~ebelp.

So how might I easily do this (I can think of a few verbose ways of doing it)? Basically I need to list POs even if there are no items (first outer join), and if there are items but no history (second outer join).

Thanks,

Chris.

Re: left outer join

kesavadas_thekkillath — Mon, 06 Sep 2010 14:23:16 GMT

use view WB2_V_EKKO_EKPO2 and then directly select the data and then use a for all entries to ekbe.

avoid the joins

Re: left outer join

Former Member — Mon, 06 Sep 2010 14:37:25 GMT

Actually your assumption is incorrect. You are allowed multiple left outer joins on an SELECT But you are not allowed to reference the fields on other tables. The error you are getting is because you are trying to JOIN EKBE using a field from EKPO. You have already defined EKPO as a left outer join.

Re: left outer join

Former Member — Tue, 07 Sep 2010 07:57:42 GMT

Yes, thanks for that. As you say, it's possible for two or more outer joins, provided a single table does not have an outer join in both directions, so to speak.