Multiple Table Join Selection

[Former Member]

In the following select statement, I just hope to exclude the PO item if delivery is complete, but when I get two same item (same material) in one PO and only one is delivery completed, all 2 items were picked into internal table?!

Any suggestion?!

SELECT ekko_{lifnr ekko}ebeln ekpo_{matnr ekpo}txz01 ekko_{aedat ekpo}menge ekpo_{meins eket}wemng stpo_{idnrk mkpf}mblnr

INTO TABLE gt_prodord

FROM ekpo

JOIN ekko ON ekko_{ebeln = ekpo}ebeln

JOIN eket ON eket_{ebeln = ekpo}ebeln

AND eket_{ebelp = ekpo}ebelp

JOIN mast ON mast_{matnr = ekpo}matnr

AND mast_{werks = ekpo}werks

JOIN stpo ON stpo_{stlnr = mast}stlnr

LEFT OUTER JOIN mkpf ON ekpo_{ebeln = mkpf}bktxt

WHERE ekko~bsart = 'NB'

AND ekpo~elikz NE 'X'

AND ekpo~loekz NE 'L'

AND stpo~postp = 'L'

AND ekpo~werks BETWEEN lv_werks_l AND lv_werks_h

AND ekpo~matkl BETWEEN lv_matkl_l AND lv_matkl_h

AND ekko~lifnr BETWEEN lv_lifnr_l AND lv_lifnr_h

AND ekko~aedat BETWEEN lv_aedat_l AND lv_aedat_h

ORDER BY ekko_{lifnr ekko}ebeln.

Thanks in advance

Edited by: abapsydney on Jul 14, 2008 8:16 AM

[Former Member]

No Suggestion?!

[Rashid_Javed]

With reference to your question

[but when I get two same item (same material) in one PO and only one is delivery completed, all 2 items were picked into internal table]

I think may be system is repeating the line item whose delivery is not complete twice.

To verify this you can include like item number EKPO-EBELP in your output (Make sure to add it also in your internal table gt_prodord) and verify if it is repeated or the line items returned are truly unique?

"SELECT ekko_{lifnr ekko}ebeln

ekpo~matnr

ekpo-ebelp "Just add PO line item number

ekpo_{txz01 ekko}aedat ekpo_{menge ekpo}meins eket_{wemng stpo}idnrk mkpf~mblnr

INTO TABLE gt_prodord ...............

....................

.......................

"

This way you can check if the line items you are getting are repeated or not.

[ThomasZloch]

this link here might cause a problem:

LEFT OUTER JOIN mkpf ON ekpo~ebeln = mkpf~bktxt

I think as soon as there is more than one MKPF found with BKTXT = EBELN, you end up with multiple lines in your internal table. Also EKET can have more than one line per EKPO.

Greetings

Thomas