Skip to Content
0

Regarding linking EKPO and EKET Tables

Oct 26, 2016 at 08:18 AM

300

avatar image

Hello Experts,

i have written 1 select query using inner join involving tables EKPO and EKET.

IF it_final1 IS NOT INITIAL.
SELECT a~ebeln
a~ebelp
a~matnr
a~werks
b~etenr
b~menge
b~wemng
FROM ekpo AS a
INNER JOIN eket AS b
ON a~ebeln = b~ebeln
and a~ebelp = b~ebelp
INTO CORRESPONDING FIELDS OF TABLE it_ekpo
FOR ALL ENTRIES IN it_final1
WHERE a~matnr = it_final1-matnr
AND a~werks = it_final1-werks
AND ( a~ebeln LIKE '45%' OR a~ebeln LIKE '51%' OR a~ebeln LIKE '71%' )

AND a~loekz <> 'L'. "Deletion Indicator

AND a~elikz <> 'X'. "Delivery Completed

Now, for a particular PO, EKPO table has 2 line items and EKET table also has 2 line items with that same PO Number. Also in EKPO table, for 2nd line item, the deletion indicator is displaying 'L' (LOEKZ = 'L'). So this line item is supposed to be excluded in my it_ekpo table according to the condition given in my select query. But during debugging, it_ekpo table is displaying both the line items due to the inner join.

So what changes are to be made in the select query so that it_ekpo displays only 1 line item?

Thanks,

Satvik

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Satvik Panchal Oct 26, 2016 at 10:27 AM
0

Hello Max,

Its 1 schedule per item.

Anyways, I m getting correct output now. By mistake I had commented the condition in Select query .i.e.

a~ebelp = b~ebelp. So after uncommenting this, I m getting the correct output.

Thanks

Show 1 Share
10 |10000 characters needed characters left characters exceeded

This question still appears as "unanswered" on SCN, kindly click "Accept" on one of the answers to close this.

Thank you.

0
avatar image
Former Member Oct 26, 2016 at 09:34 AM
0

Hi

Something doesn't sound good in what you've written:

EKPO is the item of PO

EKET is the schedules of item of PO

So if there are two items (so 2 records in EKPO) for a certain PO number and there are 2 schedules for the same items, in the EKET you should have at least 3 items, not 2, so probably have you 2 schedules per item? If it's so it right you join returns 2 records else are you sure the value of LOEKZ?

Max

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hello Max,

Thanks for replying.

LOEKZ value is 'L' for 2nd line item only.

Actually I m calculating open PO quantity from EKET table. (EKET-MENGE - EKET-WEMNG) by linking it with EKPO Table.

0
avatar image
Former Member Oct 26, 2016 at 10:22 AM
0

Hi

the join returns as many lines as records are in EKET, I mean the number of selected lines depends on EKET, so how many schedules are per iterm?

Share
10 |10000 characters needed characters left characters exceeded