For all enteries

[Former Member]

what is the exacct use of FOR ALL ENTERIES IN SELECT STATEMENT WITH SOME EXAMPLES?

[RichHeilman]

<i>

Variant 2

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

Example

Displaying the occupancy of flights on 28.02.1995:

TYPES: BEGIN OF FTAB_TYPE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

END OF FTAB_TYPE.

DATA: FTAB TYPE STANDARD TABLE OF FTAB_TYPE WITH

NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

RATIO TYPE P DECIMALS 3,

WA_SFLIGHT TYPE SFLIGHT.

• Suppost 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 = '19950228'.

RATIO = WA_SFLIGHT-SEATSOCC / WA_SFLIGHT-SEATSMAX.

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

ENDSELECT.

• The statement has the same effect as:

SELECT DISTINCT * FROM SFLIGHT INTO WA_SFLIGHT

WHERE ( CARRID = 'LH' AND

CONNID = '2415' AND

FLDATE = '19950228' ) OR

( CARRID = 'SQ' AND

CONNID = '0026' AND

FLDATE = '19950228' ) OR

( CARRID = 'LH' AND

CONNID = '0400' AND

FLDATE = '19950228' ).

RATIO = WA_SFLIGHT-SEATSOCC / WA_SFLIGHT-SEATSMAX.

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

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 internaln table itab does not have to have a header line.

The line structure of the internal table iab must be a structure. Each component of the structure that appears in the WHERE condition as a replacement symbol must have exactly the same type and length as the corresponding component of the table work area (see TABLES).

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.

You cannot use the internal table itab in the INTO clause as well.

Notes

Performance:

Specify all conditions in the WHERE clause. This means that you do not transport redundant data over the network only to filter it out in your program (using CHECK, for example).

If you regularly use a SELECT statement, you should create an index. In the WHERE clause, you should use the fields of the index, linked using AND, and checking for equality. Fields of an index that occur after a field for which you do not use an equality comparison in the WHERE clause (EQ or 😃 cannot be used to restrict the search.

The logical expression NOT in a WHERE clause is not supported by indexes. For example, WHERE FLDATE >= '19950228' is better than WHERE NOT FLDATE < '19950228'.

</i>

Regards,

Rich Heilman

[Former Member]

Hi,

FOR ALL ENTRIES IN itab WHERE cond

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

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.

Get the help by pressing F1 on Fpr all entries