Coupling INNER JOIN with FOR ALL ENTRIES statement

[Former Member]

Hi All,

I am coupling INNER JOIN with FOR ALL ENTRIES statement .....

Would you please highlight its implications ?? Is it a best practise ?

Is it advicable to use MULTIPLE INNER JOINs with a FOR ALL ENTRIES ???

SORT itab BY matnr.

IF NOT itab[] IS INITIAL.

SELECT ep_{matnr ep}ebeln ep~ebelp

ep_{werks ep}menge ep~netpr

ek_{ps_psp_pnr eb}belnr eb~menge

INTO TABLE iekpo

FROM ekpo AS ep

INNER JOIN ekkn AS ek

ON ek_{ebeln = ep}ebeln

AND ek_{ebelp = ep}ebelp

INNER JOIN ekbe AS eb

ON eb_{ebeln = ep}ebeln

AND eb_{ebelp = ep}ebelp

AND eb~bwart = '101'

FOR ALL ENTRIES IN itab

WHERE ep~matnr = itab-matnr.

IF sy-subrc EQ 0.

SORT iekpo BY matnr werks.

LOOP AT itab ASSIGNING <itab>.

READ TABLE iekpo WITH KEY matnr = <itab>-matnr

werks = <itab>-werks

BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE iekpo-matnr TO itab1-matnr.

MOVE iekpo-ebeln TO itab1-ebeln.

MOVE iekpo-ebelp TO itab1-ebelp.

MOVE iekpo-netpr TO itab1-poprice.

MOVE iekpo-werks TO itab1-werks.

MOVE iekpo-menge TO itab1-menge1.

MOVE iekpo-menge1 TO itab1-menge2.

MOVE iekpo-belnr TO itab1-belnr.

MOVE iekpo-ps_psp_pnr TO itab1-pspel.

MOVE <itab>-pspel TO itab1-tpspel.

MOVE <itab>-sobkz TO itab1-sobkz.

MOVE <itab>-fo_qty TO itab1-fo_qty.

MOVE <itab>-schgt TO itab1-schgt.

MOVE <itab>-postp TO itab1-postp.

MOVE <itab>-beskz TO itab1-beskz.

pend_qty = iekpo-menge1 - iekpo-menge2.

MOVE pend_qty TO itab1-pending.

APPEND itab1.

pend_qty = 0.

ENDIF.

ENDLOOP.

ENDIF.

ENDIF.

ENDIF.

Regards

Jaman

Edited by: ABAP Techie on Sep 15, 2008 12:39 PM

Edited by: ABAP Techie on Sep 15, 2008 12:41 PM

[Former Member]

Hi All,

I am coupling INNER JOIN with FOR ALL ENTRIES statement .....

Would you please highlight its implications ?? Is it a best practise ?

Is it advicable to use MULTIPLE INNER JOINs with a FOR ALL ENTRIES ???

SORT itab BY matnr.

IF NOT itab[] IS INITIAL.

SELECT ep_{matnr ep}ebeln ep~ebelp

ep_{werks ep}menge ep~netpr

ek_{ps_psp_pnr eb}belnr eb~menge

INTO TABLE iekpo

FROM ekpo AS ep

INNER JOIN ekkn AS ek

ON ek_{ebeln = ep}ebeln

AND ek_{ebelp = ep}ebelp

INNER JOIN ekbe AS eb

ON eb_{ebeln = ep}ebeln

AND eb_{ebelp = ep}ebelp

AND eb~bwart = '101'

FOR ALL ENTRIES IN itab

WHERE ep~matnr = itab-matnr.

IF sy-subrc EQ 0.

SORT iekpo BY matnr werks.

LOOP AT itab ASSIGNING <itab>.

READ TABLE iekpo WITH KEY matnr = <itab>-matnr

werks = <itab>-werks

BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE iekpo-matnr TO itab1-matnr.

MOVE iekpo-ebeln TO itab1-ebeln.

MOVE iekpo-ebelp TO itab1-ebelp.

MOVE iekpo-netpr TO itab1-poprice.

MOVE iekpo-werks TO itab1-werks.

MOVE iekpo-menge TO itab1-menge1.

MOVE iekpo-menge1 TO itab1-menge2.

MOVE iekpo-belnr TO itab1-belnr.

MOVE iekpo-ps_psp_pnr TO itab1-pspel.

MOVE <itab>-pspel TO itab1-tpspel.

MOVE <itab>-sobkz TO itab1-sobkz.

MOVE <itab>-fo_qty TO itab1-fo_qty.

MOVE <itab>-schgt TO itab1-schgt.

MOVE <itab>-postp TO itab1-postp.

MOVE <itab>-beskz TO itab1-beskz.

pend_qty = iekpo-menge1 - iekpo-menge2.

MOVE pend_qty TO itab1-pending.

APPEND itab1.

pend_qty = 0.

ENDIF.

ENDLOOP.

ENDIF.

ENDIF.

ENDIF.

Regards

Jaman

Edited by: ABAP Techie on Sep 15, 2008 12:39 PM

Edited by: ABAP Techie on Sep 15, 2008 12:41 PM

[Former Member]

why r u using inner joins & for all entries at a time.

use only FOR ALL ENTRIES.

Firtst find relation between the table & use for all entries.

[ThomasZloch]

I don't see a problem with it as long as qualified keys (primary, secondary, foreign) are being used for linking the tables and in the WHERE conditions. As always.

Thomas

[Former Member]

I really don't know if this will help, but you could take the condition:

AND eb~bwart = '101'

out of the JOIN condition and put it into the WHERE.

Rob

[Former Member]

.

[Former Member]

best practise ... don't know ... it is allowed and o.k.

If possible you should of coourse to have no FOR ALL ENTRIES at all !

Joins, there is no general rule, check indexes etc.

The first SORT, I don't that it help for anything, use it together with the delete adjacent duplicates if you expect duplicates in the driver table.

o.k., it can help, if there is a loop afterwards and an append inside, because the new table itab1 is then sorted.

Siegfried

[HermannGahm]

Hi,

well I see such coding in many places. Sometimes with and sometimes without problems.

If your statement is too slow a deeper analysis on detailed execution should be done to

see if a or what kind of improvement is possible.

Kind regards,

Hermann