SAP-ABAP

[Former Member]

Hai experts,

Plz explan about usage of For All Entries in Select Stmt?

wt are the pre-requisities for for all entries?

Can we get the data from more than two tables using For All Entries? How?

PLz Explain these things?

[Former Member]

Hai experts,

Plz explan about usage of For All Entries in Select Stmt?

wt are the pre-requisities for for all entries?

Can we get the data from more than two tables using For All Entries? How?

PLz Explain these things?

[Former Member]

Hi,

select for all entries is used when you want to select the data into one table comparing the entries fo another table.It is usually used in place of joins when you want to populate an internal table with the data from 2 different tables.

for that:

select <field1> <field2> from <db>

into table itab1

where key1 = <key1-value>.

select <field1> <field2> from <db>

into table itab2

for all entries in itab1

where key2 = <key2-value>.

loop at itab1.

move-corresponding itab1 to itab-final.

read table itab2 with key key2 = <key2-value>.

if sy-subrc = 0.

move-corresponding itab2 to itab-final.

endif.

append itab-final.

endloop.

[Former Member]

Check the sap provided documentation for for al entries:

<b>FOR ALL ENTRIES WHERE

Syntax

... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

Effect

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

The internal table itab must have a structured line type and the component comp must be compatible with the column col.

The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

Notes

In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.

Example

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

END OF entry_tab_type.

DATA: entry_tab TYPE TABLE OF entry_tab_type,

sflight_tab TYPE SORTED TABLE OF sflight

WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE entry_tab

WHERE cityfrom = p_city.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

FOR ALL ENTRIES IN entry_tab

WHERE carrid = entry_tab-carrid AND

connid = entry_tab-connid.</b>

REgards,

Ravi

[Former Member]

Hai Ravi,

Thankyou for ur reply,

Plz Explain prerequisities for for All Entries?

[rahulkavuri]

joins will worse your performance in most of the cases so it is advisable to avoid joins.u can instead use FOR ALL ENTRIES variant to your SELECT clause..

use select statement with for ALL ENTRIES OPTION.

In the majority of cases inner joins will actually be the requirement, but in the minority of cases you will want to use an outer join. Outer joins are useful where you may need all records from a certain table, that meet the selection criteria, and all data from another table(s), IF it exists. But if the latter data does not exist, you still want to get the first table data.

Using outer joins places a heavy load on the database engine (although no heavier that if you coded a nested select), so be very parsimonious about the use of outer joins.

The performance of the join depends on the database optimizer used especially if there are more than two tables used for joins.

Try to give maximum number of conditions in the ON clause. This is because the ON conditions are evaluated first and the virtual table created as a result is the one on which the WHERE clause applies.

Use subqueries if possible.

Make sure you follow this :

Consolidated selections

No row by row processing

No check statements

No selections within loops

Selections are 'into' internal tables - no appends

SQL trace check completed

All programs checked to make sure that they are using the full index and in the

Correct order.

Minimum or zero number of identical selects.

Use of appropriate Indexes

check this code for your problem .

A real life scenario that has been optimized.

<b>Data : Begin of I_vbak occurs 0,

Vbeln like vbak-vbeln,

End of I_vbak.

Data : Begin I_vbap occurs 0,

Vbeln like vbap-vbeln,

Posnr like vbap-posnr,

Matnr like vbap-matnr,

Kwmeng like vbap-kwmeng,

End of I_vbap.

Data : Begin of I_makt occurs 0,

Matnr like makt-matnr,

Maktx like makt-maktx,

End of I_makt.

Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap , I_makt.

Select vbeln from VBAK into table I_vbak

Where erdat = p_erdat And

vkorg = p_vkorg And

vtweg = p_vtweg And

spart = p_spart.

Clear d_lines.

Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select vbeln posnr matnr kwmeng from VBAP into table I_vbap

For all entries in table I_vbak

Where vbeln = I_vbak-vbeln.

Clear d_lines.

Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Sort I_vbap by vbeln matnr.

Select matnr maktx from makt into I_makt

For all entries in I_vbap

Where spras = sy-langu And

matnr = I_vbap-matnr.

Sort I_makt by matnr.

Loop at I_vbap.

Clear I_makt.

Read I_makt with key matnr = I_vbap-matnr binary search.

Write 😕 I_vbnap-vbeln,

I_vbap-psonr,

I_vbap-matnr,

I_Makt-matnr,

I_vbap-kwmeng.

Endloop.</b>

[Former Member]

Hi,

Always use the following check before the FOR ALL ENTRIES clause.

If not lit_itab[] is initial.

• Write the select query.

ENDIF.

Otherwise it will go into infinite loop if the internal table is initial.

<b>Reward points if it helps.</b>

[Former Member]

hii

Prerequiste for all entries

<b>Before using FOR ALL THE ENTRIES statements, check the internal table is not initial (if IT_KNA1 IS NOT INITIAL), else it will select for all the records.</b>

we can use as many tables as possible.. but more than three is not advisable ..

The same is addressed in the following link

<b>

<b></b>

Reward points if helpful

Revert back for more help

Regards

Naresh

[Former Member]

Hi Srinivas,

Select for all entries is used for performance optimization. We can avoid a loop using this

Eg.

SELECT belnr gjahr augdt augbl bukrs

blart bldat xblnr lifnr wrbtr shkzg

FROM bsak

INTO CORRESPONDING FIELDS OF TABLE i_data

FOR ALL ENTRIES IN i_doctype

WHERE blart EQ i_doctype-blart

AND augdt IN s_augdt

AND ( bukrs = '1000' OR bukrs ='4200' OR

( bukrs = '8000' AND blart NE 'ZP' ) ).

This in effect, picks up only those records in database from table bsak whose document type is there as an entry in the internal table i_doctype.

The pre requisite is that the internal table used (i_doctype) and database table from where we are selecting should have a common field.

Regards,

Susmitha.

Reward points for useful answers.

[Former Member]

hi srinivas,

eg:

select matnr from mara

into table i_matnr

where mtart in s_mtart.

<b>if i_matnr is not initial.</b>( Main prerequiste: checking the first table is initial or not.)

select matnr maktx

from makt

into table i_makt

for all entries in i_matnr

where matnr = i_matnr-matnr.

check this thread also.

thanks,

priya.

[Former Member]

Hi,

It's better to use all entries always instead of Join...

kindly reqrds the points....

How to use for 2 table... it's simple...

data : begin of it_lqua occurs 0.

include structure lqua.

data : end of it_lqua.

select single * from t320

where werks = p_werks

and lgort in s_lgort.

select * from lqua client specified

into corresponding fields of table it_lqua

where mandt = sy-mandt

and lgnum = t320-lgnum

and lgort = t320-lgort.

select * from makt client specified

into corresponding fields of table it_makt

for all entries in it_lqua

where mandt = sy-mandt

and matnr = it_lqua-matnr

and spras = sy-langu.

select * from lagp client specified

into corresponding fields of table it_lagp

for all entries in it_lqua

where mandt = sy-mandt

and lgnum = it_lqua-lgnum

and lgtyp = it_lqua-lgtyp

and lgpla = it_lqua-lgpla

and lgber in s_lgber.

while calling it_lqua if it_lqua is empty then no records would be found.....

Like above u would do the cdoing and add more tables...

and int he final intrnal table u would read all the data into final internal table...