ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT → SELECT - WHERE
WHERE - FOR ALL ENTRIES
Other versions: 7.31 | 7.40 | 7.54
... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...
If the addition
FOR ALL ENTRIES is specified before the language element
WHERE of the
SELECT statement, the components
comp of the internal table
itab specified there
can be used in
sql_cond as the operands of comparisons with
relational operators. 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
table_line must be specified for
comp. The name of the host variable
dbcur should be prefixed with the escape character
The entire logical expression
is evaluated for each individual row of the internal table
itab. The result
set of the
SELECT statement is the union set of the result sets produced
by the individual evaluations. Rows that appear in duplicate are removed from the result 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 result set.
The logical expression
WHERE condition can comprise multiple logical expressions using
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. In a
FOR ALL ENTRIES, the addition
ORDER BY can only be used with the addition
The following restrictions apply when using the
FOR ALL ENTRIES addition with other additions:
- The addition
FOR ALL ENTRIESis only possible before
WHEREconditions of the
- The addition
FOR ALL ENTRIEScannot be used with the addition
- If the addition
FOR ALL ENTRIESis used, no LOB handles can be created as reader streams or as locators in the
- The addition
FOR ALL ENTRIESshould not be used with the addition
GROUP BY. The addition
GROUP BYhas no effect if
FOR ALL ENTRIESis used.
- The same internal table can be specified after
FOR ALL ENTRIESand after
INTO. The content of the table is evaluated by
FOR ALL ENTRIESand then overwritten by the
- A comparison with a column of an internal table can also be performed using the
WHEREcondition of a subquery for the same database table or view.
- With respect to duplicate rows in the results set, the addition
FOR ALL ENTRIEShas the same effect as when the addition
DISTINCTis 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 result set on the application server. The duplicate rows are then removed from the database system if the
SELECTstatement can be passed to the database system as a single SQL statement. If the
SELECTstatement has to be distributed to multiple SQL statements, the aggregation takes place on the application server.
- The addition
FOR ALL ENTRIESbypasses SAP buffering for tables with generic buffering if the condition after
FOR ALL ENTRIESprevents a single generic area from being specified exactly.
FOR ALL ENTRIEScan be a more efficient alternative to join expressions.
- If duplicated rows are first removed from the application server, all rows specified by the
WHEREcondition (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
UP TO n ROWSis used simultaneously. These then have no effect on the number of rows passed from the database server to the application server, but are only used 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
itabafter 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.
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
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
is not initial before the
SELECT statement is executed using
FOR ALL ENTRIES.
PARAMETERS p_city TYPE spfli-cityfrom. 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 = @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 INTO CORRESPONDING FIELDS OF TABLE @result_tab. ENDIF.