SAP WHERE ALL ENTRIES
Get Example source ABAP code based on a different SAP table
• FOR ALL ENTRIES SELECT
SELECT, FOR ALL ENTRIES Short Reference
ABAP_SYNTAX
... FOR ALL ENTRIES IN @itab WHERE ... col operator @itab-comp ...
What does it do?
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 as a host variable can be used in relational expressions within sql_cond on the right side of a comparison operator in comparisons with a column col. 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 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 occur more than once are removed from the result set automatically. The entire content of a row is considered here.
ABAP_CAUTION If the internal table itab is empty, the entire WHERE condition is ignored. This means that none of the rows in the database table are skipped and are placed in the result set once any duplicate rows are removed.
The logical expression sql_cond of the WHERE condition can comprise multiple relational 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 used in combination with SQL expressions, except for columns specified individually or an aggregate expression COUNT( * ) specified individually.
If the addition FOR ALL ENTRIES is used, no LOB handles can be created as reader streams or as locators in the target area of a standalone SELECT statement.
The addition FOR ALL ENTRIES cannot be combined with UNION, INTERSECT, and EXCEPT.
In a SELECT statement with FOR ALL ENTRIES, no aggregate expressions except for COUNT( * ) can be used in the SELECT list. In cases like these, the aggregate expression is not evaluated in the database, but is emulated on the AS ABAP.
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 must be in the SELECT list, except for the client column in client-dependent tables.
No path expressions can be used in a SELECT statement with FOR ALL ENTRIES.
If the addition FOR ALL ENTRIES is used, the SELECT list should not contain any database fields of the built-in types STRING, RAWSTRING, and GEOM_EWKB as well as LCHR and LRAW. These data types prevent rows that occur more than once on the database system from being removed. These rows are only removed from the result set on the AS ABAP. If listed in the SELECT list, a syntax check warning is raised that can be hidden by a pragma.
The internal table itab is evaluated once for each query. Any changes made to the content of the internal table in a SELECT loop or WITH loop are ignored by the logical expression.
BEGIN_SECTION SAP_INTERNAL_HINT
There is no restriction to the size of itab. The SELECT statement sent to the database never becomes too large. If necessary, it is split to several statements and the results are combined internally.
END_SECTION SAP_INTERNAL_HINT
Latest 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 rows occurring more than once in the result 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 by the database system and are sometimes only deleted from the result set on AS ABAP instead. 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 and the addition DISTINCT is supported. If the SELECT statement needs to be distributed to multiple SQL statements before it is passed or if columns of the types STRING and RAWSTRING plus LCHR and LRAW are specified in the SELECT list , the rows are aggregated on AS ABAP.
If duplicate rows are only removed from AS ABAP, 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 regular internal tables. More specifically, the system table is always required if one of the additions PACKAGE SIZE, UP TO, or OFFSET is used simultaneously. These then have no effect on the number of rows passed from the database server to AS ABAP 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.
BEGIN_SECTION SAP_INTERNAL_HINT
The internal system table is created if: The target area is not an internal table, the addition APPENDING TABLE is used, the target table contains columns that do not correspond to database columns, the additions PACKAGE SIZE or UP TO n ROWS are used.
END_SECTION SAP_INTERNAL_HINT
If the column types allow it, the addition DISTINCT might be used together with FOR ALL ENTRIES in order to explicitly express the behavior.
The addition FOR ALL ENTRIES bypasses table 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, table buffering is used and the addition FOR ALL ENTRIES can be a more efficient alternative to join expressions.
It is strongly recommended that the internal table itab is not initial before using it after FOR ALL ENTRIES. In case of an initial internal table, all rows are read from the database regardless of any further conditions specified after WHERE, which is generally not desired.
It depends on the database platform and different settings, how the content of the internal table is passed to the database and where it is transformed into conditions. If FDA write access is available, it can be used by FOR ALL ENTRIES.
NON_V5_HINTS
If, in a strict mode of the syntax check , FOR ALL ENTRIES is specified together with columns of the types STRING and RAWSTRING plus LCHR and LRAW in the SELECT list, the syntax check is performed in ABAP_STRICT_768 strict mode from ABAP_RELEASE ABAP_768 / .
If the entire WHERE condition is ignored because the internal table itab is empty, the implicit WHERE condition for the current client or the client specified using USING CLIENT is not affected, if implicit client handling is enabled. This means that all data is only read from the current client.
If implicit client handling is disabled using the obsolete addition CLIENT SPECIFIED, no implicit WHERE condition exists for the client. Any WHERE condition specified explicitly for the client column is ignored with the entire condition if the internal table itab is empty and the data from all clients is read.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
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. The table entry_tab must not be initial before the SELECT statement is executed using FOR ALL ENTRIES. The addition DISTINCT is used to explicitly express the implicit behavior.
ABEXA 01401
ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5
Use of FOR ALL ENTRIES with an empty internal table. All rows of the DDIC database table are read. The number of read rows is usually smaller in the second SELECT statement than in the first statement. This is because only one column is read, and hence more duplicate rows can be removed. The first SELECT statement, on the other hand, reads all rows of the DDIC database table to the result set, since the columns cover the entire table key.
ABEXA 01402
ABAP_EXAMPLE_END
