Forallentries

[Former Member]

why we are using for all entries?

[Former Member]

why we are using for all entries?

[amit_khare]

Hi,

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,

Amit

[Former Member]

Variant 12

... FOR ALL ENTRIES IN itab WHERE cond

Effect

Selects only those lines of the database table which satisfy the WHERE condition cond where each occurring replacement symbol itab-f is replaced by the value of the component f in the internal table itab for at least one line. Clearly, a SELECT command with ... FOR ALL ENTRIES IN itab WHERE cond forms the union of solution sets for all SELECT commands which result when, for each line of the internal table itab , each symbol itab-f addressed in the WHERE condition is replaced by the relevant value of the component f in this table line. Duplicate lines are eliminated from the result set. If the internal table itab contains no entries, the processing continues as if the WHERE condition cond has failed.

Example

Display a full list of flights on 28.02.1995:

TABLES SFLIGHT.

DATA: BEGIN OF FTAB OCCURS 10,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

END OF FTAB,

RATIO TYPE F.

• Let FTAB be filled as follows:

*

• CARRID CONNID

• --------------

• LH 2415

• SQ 0026

• LH 0400

SELECT * FROM SFLIGHT FOR ALL ENTRIES IN FTAB

WHERE CARRID = FTAB-CARRID AND

CONNID = FTAB-CONNID AND

FLDATE = '19950228'.

RATIO = SFLIGHT-SEATSOCC / SFLIGHT-SEATSMAX.

WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, RATIO.

ENDSELECT.

Notes

... FOR ALL ENTRIES IN itab WHERE cond can only be used with a SELECT command.

In the WHERE condition ... FOR ALL ENTRIES IN itab WHERE cond , the symbol itab-f always has the meaning of a replacement symbol and must not be confused with the component f of the header line in the internal table itab . The internal table itab does not have to have a header line.

The line structure of the internal table itab must be a field string. Each component of this field string which occurs in a replacement symbol in the WHERE condition must be of exactly the same type and length as the corresponding component in the table work area (see TABLES ).

Replacement symbols must not occur in comparisons with the operators LIKE , BETWEEN and IN .

FOR ALL ENTRIES IN itab excludes ORDER BY f1 ... fn in the ORDER-BY clause .

The internal table itab cannot be used at the same time in the INTO clause .

[Former Member]

For all entries are used when you want to retrieve data from table 2 based upon the entries in table 1.

Regards,

Dharitree

[Former Member]

Hi,

If suppose u have two get values from two tables. first u take values into one internal table and based on first internal table primay key values u have to get values from other transparent table.using for all entries u can get it. with less time.

else u have to loop that internal table and then get those values.which is bad when performance issues are considered.

[Former Member]

hi,

check this is from help,

... FOR ALL ENTRIES IN itab WHERE cond

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. 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).

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

END OF ftab_type.

DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH

NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

free TYPE I,

wa_sflight TYPE sflight.

• Suppose FTAB is filled as follows:

• CARRID CONNID

• --------------

• LH 2415

• SQ 0026

• LH 0400

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

• The statement has the same effect as:

SELECT DISTINCT * FROM sflight INTO wa_sflight

WHERE ( carrid = 'LH' AND

connid = '2415' AND

fldate = '20010228' ) OR

( carrid = 'SQ' AND

connid = '0026' AND

fldate = '20010228' ) OR

( carrid = 'LH' AND

connid = '0400' AND

fldate = '20010228' ).

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

Notes

You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.

In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.

Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.

You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.

If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.

If you use FOR ALL ENTRIES IN itab, you cannot use a HAVING clause as well.

hope this helps,

do reward if it helps,

priya.

[Former Member]

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.

[Former Member]

Suppose we have one internal table in which we have selected the required data, e. g. all the sales order numbers. Now we want to select items in these sales order numbers. So instead of using loop at itab. Select..endloop, which will affect the performance. we can use

select * from VBAP for all entries in itab where vbeln = itab-vbeln.

Have a look at Tabular Conditions in below link.

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

Best Regards,

Vibha Deshmukh

*Plz mark useful answers

[anversha_s]

hi,

FOR ALL ENTRIES is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

---

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

---

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

---

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

4

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

bukrs LIKE t001-bukrs,

END OF itab.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

*----

---

itab-bukrs = '1000'.

APPEND itab.

itab-bukrs = '1100'.

APPEND itab.

*----

---

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

*----

---

LOOP AT t001.

WRITE 😕 t001-bukrs.

ENDLOOP.

Hope this helps!

Regards,

anver

if hlped mark points