for all entries

[Former Member]

dear exparts

could u explain about 'for all entries' key word?

[rodrigo_paisante3]

[FOR ALL ENTRIES!|https://wiki.sdn.sap.com/wiki/display/ABAP/Performance]

[Former Member]

Hi,

SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

for example:

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

this condition, return all entries of the sflight

Regards,

Chithra

[Former Member]

hi ,

check this program...

&----

---

*& Report ZZZZ000000

*&

&----

---

*&

*&

&----

---

REPORT ZZZZ000000.

tables:mara,marc,mard,makt.

data:begin of it_mara occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

end of it_mara.

data:begin of it_marc occurs 0,

matnr like marc-matnr,

pstat like marc-pstat,

werks like marc-werks,

end of it_marc.

data:begin of it_mard occurs 0,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of it_mard.

data:begin of it_final occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

pstat like marc-pstat,

werks like marc-werks,

lgort like mard-lgort,

labst like mard-labst,

maktx like makt-maktx,

end of it_final.

select-options:s_matnr for mara-matnr.

select matnr

mtart

meins

from mara

into table it_mara

where matnr in s_matnr.

if not it_mara[] is initial.

select matnr

pstat

werks

from marc

into table it_marc

for all entries in it_mara

where matnr = it_mara-matnr.

if not it_marc[] is initial.

select werks

lgort

labst

from mard

into table it_mard

for all entries in it_marc

where werks = it_marc-werks.

endif.

endif.

loop at it_mara.

it_final-matnr = it_mara-matnr.

it_final-mtart = it_mara-mtart.

it_final-meins = it_mara-meins.

read table it_marc with key matnr = it_mara-matnr.

it_final-werks = it_marc-werks.

it_final-pstat = it_marc-pstat.

read table it_mard with key werks = it_marc-werks.

it_final-lgort = it_mard-lgort.

it_final-labst = it_mard-labst.

if sy-subrc = 0.

select maktx from makt into it_final-maktx where matnr = it_final-matnr.

endselect.

endif.

append it_final.

endloop.

loop at it_final.

write:/ it_final-matnr under 'material',

it_final-mtart under 'material type',

it_final-meins under 'unit of measure',

it_final-werks under 'plant' ,

it_final-pstat under 'status',

it_final-lgort under 'storage loc',

it_final-labst under 'stock',

it_final-maktx.

endloop.

regards,

venkat.

[Former Member]

The FOR ALL ENTRIES clause is used when you need to restrict your selection to the values of other tables that have already been loaded, what means data you need to compare will be on an internal table.

SELECT connid
FROM spfli
FOR ALL ENTRIES IN gt_scarr
WHERE carrid = gt_scarr-carrid.

Where gt_scarr is an internal table that has been already selected from the database.

This helps on performance issues.

[Former Member]

Hi,

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at mat_gp.

Select single * from mara into itab

where matnr = itab-matnr.

Append itab.

Endloop.

Recommended

Select * from mara appending table itab

For all entries in mat_gp

Where matnr = mat_gp-matnr.

Thanks,

A.Melchizedek