FOR ALL ENTRIES

[Former Member]

Hi,

Can anyone explain FOR ALL ENTRIES concept with Example

And what is the difference between for all entries and inner join

Thanks & Regards,

Maya

[Former Member]

Hi Maya,

we are using select for all entries to avoid inner joins..

suppose

select vbeln from vbak into corresponding fields of table itab.

if not itab[] is initial.

select <fields> from vbap into corresponding fields of table itab1 for all entries in itab where vbeln = itab-vbeln.

endif.

it will fetch the data by comparing vbeln of itab and vbap but one thing you have to check itab is initial or not if it is not initial(not contain any data) then you should not use for all entries it will fetch all the values from vbap.

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Select using JOINS

The plus

Very large amount of data

Similar to Nested selects - when the accesses are planned by the programmer

In some cases the fastest

Not so memory critical

The minus

Very difficult to program/understand

Mixing processing and reading of data not possible

For further reference regarding optimization you can look into this link....

http://www.sapbrain.com/ARTICLES/TECHNICAL/optimization/optimization.html#Optimizing%20the%20load%20...

Hope this resolves your query.

<b>Reward all the helpful answers.</b>

Regards

[Former Member]

Hi,

It is used to fetch the data from a DB table with respect to the entries already exist in an internal table:

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

reward points if useful

regards,

ANJI

[sreeramkumar_madisetty]

Hi

For clear understanding on these topics,

Please refer the link:

http://www.sapbrain.com/ARTICLES/TECHNICAL/optimization/optimization.html#Optimizing%20the%20load%20...

Regards,

Sreeram

[Former Member]

Hi

In replacement of joins we r using for all entries. Check out this ex.

Data: Begin of it_lfa1 occurs 0,

lifnr like lfa1-lifnr,

name1 like lfa1-name1,

land1 like lfa1-land1,

End of it_lfa1.

Data: Begin of it_kna1 occurs 0,

kunnr like kna1-kunnr,

name1 like kna1-name1,

land1 like kna1-land1,

End of it_kna1.

select lifnr

name1

land1

from lfa1

into table it_lfa1.

if not it_lfa1[] is initial.

select kunnr

name1

land1

from kna1

into table it_kna1

for all entries in it_lfa1

where lifnr = it_lfa1-lifnr.

endif.

loop at it_kna1.

write:/ it_kna1-kunnr,it_kna1-name1,it_kna1-land1.

endloop.

Regarsd

Haritha.

[Former Member]

Hi,

FOR ALL ENTRIES are usefull while going for somany inner joins.

insted of making big query & fetching all records from database, we are fetching minimum records in first query, then the next query we are using output of first query recordset in where clause.

But before using for all entries we have to eleminate redundent records and make sure that first query output Internal table is having any data. otherwise it will fetch all the records in the tabel.

if Itab1 is not Initial.

execute second query with FOR ALL ENTRIES.

endif.

if u have more doubts reply, i will send examples.

regards

V.Rajesh