topic Problem in join statement in Application Development and Automation Discussions

Problem in join statement

Former Member — Wed, 21 Nov 2007 16:11:29 GMT

Hi,

I have the following join cond on ekko, ekpo, ekkn tables.


  SELECT m~ebeln m~procstat m~rlwrt m~frgke m~bukrs
         c~ebelp c~txz01 c~knttp c~netwr
         c1~nplnr c1~kostl c1~anln1 c1~sakto c1~prctr c1~aufnr c1~imkey c1~ps_psp_pnr c1~zekkn

         FROM  ekko AS m INNER JOIN ekpo AS c ON
         c~ebeln = m~ebeln

         INNER JOIN ekkn AS c1 ON
         c1~ebeln = m~ebeln AND
         c1~ebelp = c~ebelp

         INTO CORRESPONDING FIELDS OF TABLE it_outtab

         WHERE m~bukrs IN s_bukrs AND
               m~bsart IN s_bsart AND
               m~aedat IN s_date AND
               m~lifnr IN s_lifnr AND
               m~ebeln IN s_ebeln AND
               c1~kostl IN s_kostl AND
               c1~ps_psp_pnr IN s_posid AND
               c1~nplnr IN s_aufnr AND
               c1~aufpl IN s_vornr.

Now my problem is whenever there are no enteries in ekpo or ekkn table i'ts not showing those PO's in the final output bcoz I have used inner join logic. But I want those PO'S also. How can i do that.

Thanks,

Re: Problem in join statement

Former Member — Wed, 21 Nov 2007 16:17:04 GMT

Hi,

Use OUTER JOIN.

Regards,

Satish

Re: Problem in join statement

naimesh_patel — Wed, 21 Nov 2007 16:23:44 GMT

Change your query like this:

  SELECT m~ebeln m~procstat m~rlwrt m~frgke m~bukrs
         c~ebelp c~txz01 c~knttp c~netwr
         c1~nplnr c1~kostl c1~anln1 c1~sakto c1~prctr c1~aufnr c1~imkey c1~ps_psp_pnr c1~zekkn
 
         FROM  ekko AS m LEFT OUTER JOIN ekpo AS c ON    " << LEFT OUTER
         c~ebeln = m~ebeln
 
         INNER JOIN ekkn AS c1 ON
         c1~ebeln = m~ebeln AND
         c1~ebelp = c~ebelp
 
         INTO CORRESPONDING FIELDS OF TABLE it_outtab
 
         WHERE m~bukrs IN s_bukrs AND
               m~bsart IN s_bsart AND
               m~aedat IN s_date AND
               m~lifnr IN s_lifnr AND
               m~ebeln IN s_ebeln AND
               c1~kostl IN s_kostl AND
               c1~ps_psp_pnr IN s_posid AND
               c1~nplnr IN s_aufnr AND
               c1~aufpl IN s_vornr.

Regards,

Naimesh Patel

Re: Problem in join statement

Former Member — Wed, 21 Nov 2007 16:27:11 GMT

Patel,

I have to change for ekkn also, coz same problem i'm facing with ekkn also. if there are no enteries in ekkn, i'm not getting bcoz of inner join.

So do i need to use outer join with ekkn also.

Pls suggest...

Re: Problem in join statement

naimesh_patel — Wed, 21 Nov 2007 16:37:33 GMT

No need.. only LEFT OUTER in the first join will work.

Put a break point after the select statment and check in debug.

Regards,

Naimesh Patel