Skip to content

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

Syntax


... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

Effect

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 pseudo component 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 sql_cond 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 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. In a SELECT statement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The following restrictions apply when using the FOR ALL ENTRIES addition with other additions:

  • The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.
  • The addition FOR ALL ENTRIES cannot be used with the addition SINGLE after SELECT.
  • 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.


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 database table or view.

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

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

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.