... FOR ALL ENTRIES IN @itab WHERE ... col operator @itab-comp ...
Effect
If the addition FOR ALL ENTRIES is specified in front of the language element WHERE of the statement SELECT of a
main query, the components
comp of the internal table itab specified here
can be used within sql_cond on the right side of comparisons of a
relational operator in comparisons with a
columncol. The specified
component comp must be compatible with the column col.
The internal table itab can have a structured or an elementary row type. For an elementary row type, the
pseudo componenttable_line must be specified for comp. The name of the host variable
dbcur should be prefixed with the escape character @.
The entire logical expression sql_cond
is evaluated for each individual row of the internal table itab. The results
set of the SELECT statement is the union set of the results sets produced
by the individual evaluations. Rows that appear in duplicate are removed from the results set automatically.
If the internal table itab is empty, the entire WHERE condition is ignored and all rows from the database are placed in the results set.
The logical expression sql_cond
of the WHERE condition can comprise multiple logical expressions 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 that can be specified statically or dynamically.
The following restrictions apply when using the addition FOR ALL ENTRIES with other additions:
The addition FOR ALL ENTRIES is only possible in front of WHERE
conditions in a standalone SELECT statement or in the
main query after OPEN CURSOR, if no common table expressions are defined using WITH.
The addition FOR ALL ENTRIES cannot be used with the addition SINGLE.
The addition FOR ALL ENTRIES cannot be combined with SQL expressions.
If the addition FOR ALL ENTRIES is used, no database fields of the predefined
types STRING and RAWSTRING plus LCHR and LRAW can occur in the SELECT list. In the
strict mode from Release 7.40, SP05,
this produces a syntax error. Otherwise, the extended program check produces a syntax warning that can be hidden by a pragma.
The addition FOR ALL ENTRIES cannot be combined with UNION.
The addition FOR ALL ENTRIES should not be used with the addition GROUP BY. The addition GROUP BY has no effect if FOR ALL ENTRIES is used.
In a SELECT statement with FOR ALL ENTRIES,
the addition ORDER BY can
only be used with the addition PRIMARY KEY and can only be used to access
a single table or view. In this case, all columns of the primary key (except for the client column in
client-specific tables) must be in the SELECT list.
No path expressions can be used in a SELECT statement with FOR ALL ENTRIES.
Notes
The same internal table can be specified after FOR ALL ENTRIES and after
INTO. The content of the table
is evaluated by FOR ALL ENTRIES and then overwritten by the INTO clause.
A comparison with a column of an internal table can also be performed using the WHERE condition of a
subquery for the same data source.
With respect to duplicate rows in the results set, the addition FOR ALL ENTRIES
has the same effect as when the addition DISTINCT
is specified in the definition of the selection set. Unlike DISTINCT, the
rows are not always deleted from the database system but instead are sometimes first deleted from the
results set on the application server. The duplicate rows are then removed from the database system
if the SELECT statement can be passed to the database system as a single
SQL statement. If the SELECT statement has to be distributed to multiple SQL statements, the aggregation takes place on the application server.
No database fields of the types STRING and RAWSTRING plus LCHR and LRAW are possible in the SELECT list when the addition
DISTINCT is specified, which means that these types cannot be specified together with
FOR ALL ENTRIES, since they prevent the statement from being executed on the database system.
The addition FOR ALL ENTRIES bypasses
SAP buffering for tables
with generic buffering if the condition after FOR ALL ENTRIES prevents a single generic area from being specified exactly.
In all other cases, SAP buffering is used and the addition FOR ALL ENTRIES can be a more efficient alternative to
join expressions.
If duplicated rows are first removed from the application server, all rows specified by the WHERE condition (in some cases) are passed to an internal system table and then aggregated. The
maximum size of this system table is restricted
to that of normal internal tables. In particular, the system table is always required if one of the
additions PACKAGE SIZE or
UP TO n ROWS is used
simultaneously. These are then ignored by the number of rows passed from the database server to the
application server and are applied only when the rows are passed from the system table to the actual target area. If the maximum size of the internal system table is exceeded, a runtime error occurs.
Before using an internal table itab after FOR ALL
ENTRIES, always check that the internal table is not initial. In an initial internal tables,
all rows are read from the database regardless of any further conditions specified after WHERE. This is not usually the required behavior.
Example
Gets all flight data for a specified departure city. The relevant airlines and flight numbers are first
passed to an internal table entry_tab, which is evaluated in the WHERE
condition of the subsequent SELECT statement. This selection could also be
carried out in a single SELECT statement by using a join in the FROM clause. Make sure that the table entry_tab
is not initial before the SELECT statement is executed using FOR ALL ENTRIES.
DATA p_city TYPE spfli-cityfrom. cl_demo_input=>request( CHANGING field = p_city ).
TYPES: BEGIN OF entry_tab_type,
carrid TYPE spfli-carrid, connid TYPE spfli-connid,
END OF entry_tab_type.
TYPES: BEGIN OF result_tab_type,
carrid TYPE sflight-carrid, connid TYPE sflight-connid,
fldate TYPE sflight-fldate, END OF result_tab_type.
DATA: entry_tab TYPE TABLE OF entry_tab_type, result_tab TYPE SORTED TABLE OF result_tab_type
WITH UNIQUE KEY carrid connid fldate.
SELECT carrid, connid FROM spfli WHERE cityfrom = @( to_upper( p_city ) )
INTO CORRESPONDING FIELDS OF TABLE @entry_tab.
IF entry_tab IS NOT INITIAL.
SELECT carrid, connid, fldate FROM sflight
FOR ALL ENTRIES IN @entry_tab WHERE carrid = @entry_tab-carrid AND
connid = @entry_tab-connid
ORDER BY PRIMARY KEY INTO CORRESPONDING FIELDS OF TABLE @result_tab.
cl_demo_output=>display( result_tab ). ENDIF.