for all entries

[former_member778253]

Hi,

When will we use for all entries?

[Former Member]

Hi

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.

<b>Reward points for useful Answers</b>

Regards

Anji

[seshatalpasai_madala]

Hi,

FOR ALL ENTRIES is to join A Database tbale with a INTERNAL Table declared in the program.

Regards,

Sesh

[Former Member]

for all entries is the subsititute of join statement. this is used to improve the performance of report.

Rewards the point if helpful.

[Former Member]

Hi Sivananda,

When we want to SELECT entries from database table, based on another INTERNAL TABLE entry, then we use FOR ALL ENTRIES.

<i><b>Reward points for useful answers.</b></i>

Best Regards,

Ram.

[Former Member]

Hi,

<b>Use of FOR ALL Entries</b>

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 int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Regards

Sudheer

[varma_narayana]

Hi..

We have to use FOR ALL ENTRIES

when we want to Fetch data from a DB Table into an internal Table by comparing the Entries of another Internal Table.

For Eg:

Tables : EKKO.

Data: IT_EKKO like table of EKKO, "Driver Table

IT_EKPO like Table of EKPO.

Parameters : p_lifnr type ekko-lifnr.

Select-options: S_BEDAT for EKKO-BEDAT.

START-OF-SELECTION.

Select * from EKKO

into table IT_EKKO

where lifnr = p_lifnr

and bedat in S_bedat.

if IT_EKKO is not intital. "Must check

SELECT * from EKPO

into TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

where EBELN = IT_EKKO-EBELN.

Endif.

Note: When the Driver Table (IT_EKKO) is empty this FOR ALL ENTRIES will fetch all records of EKKO.

So We will use FOR ALL ENTRIES when we declare Multiple internal Tables

where as we Use JOIN or View when we declare all the fields in Single Internal Table.

<b>Reward if Helpful.</b>

[Former Member]

Hi,

<u><b>For all entries</b></u>

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.

Regards,

Bhaskar