FOR ALL ENTRIES in SELECT ABAP: Examples and Checks

FOR ALL ENTRIES in SELECT ABAP is not new ABAP syntax, but it is still one of the most important Open SQL techniques for reading database records by values from an internal table ABAP. ABAP developers often use it when they already have a list of keys in memory and need to select related data from another SAP table.

The operator is powerful, but it must be used carefully. A missing empty-table check or a table with many duplicate keys can lead to wrong logic, unnecessary database load, and bad performance. In this article, we will look at a simple FOR ALL ENTRIES example, common limitations, and practical alternatives such as range tables and JOIN.

What is FOR ALL ENTRIES in ABAP?

FOR ALL ENTRIES allows a SELECT statement to use values from an internal table in the WHERE condition. You can think about it as a way to restrict the database selection by many values that are already stored in ABAP memory.

A typical use case is a two-step read: first select documents, orders, flights, or business objects; then use their keys to select related master data or detail records.

Simple FOR ALL ENTRIES example

In this example, we first read flight records from SFLIGHT. Then we use their carrier and connection values to read related records from SPFLI.

SELECT carrid, connid, fldate, price
  INTO TABLE @DATA(lt_sflight)
  FROM sflight
  WHERE seatsmax_b > 22.

IF lt_sflight IS NOT INITIAL.
  SELECT carrid, connid, countryfr, countryto
    INTO TABLE @DATA(lt_spfli)
    FROM spfli
    FOR ALL ENTRIES IN @lt_sflight
    WHERE carrid = @lt_sflight-carrid
      AND connid = @lt_sflight-connid.
ENDIF.

SELECT carrid, connid, fldate, price

INTO TABLE @DATA(lt_sflight)

FROM sflight

WHERE seatsmax_b > 22.

IF lt_sflight IS NOT INITIAL.

SELECT carrid, connid, countryfr, countryto

INTO TABLE @DATA(lt_spfli)

FROM spfli

FOR ALL ENTRIES IN @lt_sflight

WHERE carrid = @lt_sflight-carrid

AND connid = @lt_sflight-connid.

ENDIF.

The second SELECT reads only SPFLI rows that match the carrid and connid values from lt_sflight. The condition should normally compare key fields with equality. Operators such as greater than are possible in ABAP SQL, but they are rarely the right choice for a key-based FOR ALL ENTRIES read.

Always check that the internal table is not empty

The most important rule for FOR ALL ENTRIES in ABAP is simple: always check the internal table before the SELECT. If the internal table after FOR ALL ENTRIES is empty, the condition based on this table is not useful and the SELECT can return many more records than expected.

This is dangerous for two reasons. First, the result can be logically wrong. Second, the database can read a large number of rows and create a performance problem.For all entries in SAP ABAP. Limitations and checks.

SELECT carrid, connid, fldate, price
  INTO TABLE @DATA(lt_sflight_empty)
  FROM sflight
  WHERE seatsmax_b > 9999.

" Do not do this without an empty-table check
SELECT carrid, connid, countryfr, countryto
  INTO TABLE @DATA(lt_spfli_wrong)
  FROM spfli
  FOR ALL ENTRIES IN @lt_sflight_empty
  WHERE carrid = @lt_sflight_empty-carrid
    AND connid = @lt_sflight_empty-connid.

SELECT carrid, connid, fldate, price

INTO TABLE @DATA(lt_sflight_empty)

FROM sflight

WHERE seatsmax_b > 9999.

" Do not do this without an empty-table check

SELECT carrid, connid, countryfr, countryto

INTO TABLE @DATA(lt_spfli_wrong)

FROM spfli

FOR ALL ENTRIES IN @lt_sflight_empty

WHERE carrid = @lt_sflight_empty-carrid

AND connid = @lt_sflight_empty-connid.

The safe version is:

IF lt_sflight_empty IS NOT INITIAL.
  SELECT carrid, connid, countryfr, countryto
    INTO TABLE @DATA(lt_spfli_safe)
    FROM spfli
    FOR ALL ENTRIES IN @lt_sflight_empty
    WHERE carrid = @lt_sflight_empty-carrid
      AND connid = @lt_sflight_empty-connid.
ENDIF.

IF lt_sflight_empty IS NOT INITIAL.

SELECT carrid, connid, countryfr, countryto

INTO TABLE @DATA(lt_spfli_safe)

FROM spfli

FOR ALL ENTRIES IN @lt_sflight_empty

WHERE carrid = @lt_sflight_empty-carrid

AND connid = @lt_sflight_empty-connid.

ENDIF.

Remove duplicate key values before FOR ALL ENTRIES

Another common performance problem is duplicate values in the internal table. If the driving table contains many repeated key combinations, the database selection becomes unnecessarily large and harder to optimize.

A good practice is to create a smaller helper table that contains only the fields used in the FOR ALL ENTRIES condition. Then remove duplicate key combinations before the SELECT.

TYPES: BEGIN OF ty_carr_conn,
         carrid TYPE s_carr_id,
         connid TYPE s_conn_id,
       END OF ty_carr_conn.

DATA lt_carr_conn TYPE STANDARD TABLE OF ty_carr_conn.

SELECT carrid, connid, fldate, price
  INTO TABLE @DATA(lt_sflight_2)
  FROM sflight
  WHERE seatsmax_b > 22.

lt_carr_conn = VALUE #(
  FOR GROUPS key OF ls_sflight IN lt_sflight_2
  GROUP BY ( carrid = ls_sflight-carrid
             connid = ls_sflight-connid )
  ( carrid = key-carrid
    connid = key-connid )
).

IF lt_carr_conn IS NOT INITIAL.
  SELECT carrid, connid, countryfr, countryto
    INTO TABLE @DATA(lt_spfli_2)
    FROM spfli
    FOR ALL ENTRIES IN @lt_carr_conn
    WHERE carrid = @lt_carr_conn-carrid
      AND connid = @lt_carr_conn-connid.
ENDIF.

TYPES: BEGIN OF ty_carr_conn,

carrid TYPE s_carr_id,

connid TYPE s_conn_id,

END OF ty_carr_conn.

DATA lt_carr_conn TYPE STANDARD TABLE OF ty_carr_conn.

SELECT carrid, connid, fldate, price

INTO TABLE @DATA(lt_sflight_2)

FROM sflight

WHERE seatsmax_b > 22.

lt_carr_conn = VALUE #(

FOR GROUPS key OF ls_sflight IN lt_sflight_2

GROUP BY ( carrid = ls_sflight-carrid

connid = ls_sflight-connid )

( carrid = key-carrid

connid = key-connid )

IF lt_carr_conn IS NOT INITIAL.

SELECT carrid, connid, countryfr, countryto

INTO TABLE @DATA(lt_spfli_2)

FROM spfli

FOR ALL ENTRIES IN @lt_carr_conn

WHERE carrid = @lt_carr_conn-carrid

AND connid = @lt_carr_conn-connid.

ENDIF.

This example uses new ABAP syntax only to prepare the helper internal table. The important idea is independent of syntax: select with the smallest possible set of relevant key values.

Replace FOR ALL ENTRIES with a range table

In some cases, you can replace FOR ALL ENTRIES with a range table and the IN operator. This is usually a good option when you filter by one field only, for example a list of carrier IDs, material numbers, or document numbers.

This approach is easier to read than FOR ALL ENTRIES when only one field is involved. It also makes the intention very clear: select rows where a field is in a list of values.

DATA lt_rng_carrid TYPE RANGE OF s_carr_id.

SELECT carrid, connid, fldate, price
  INTO TABLE @DATA(lt_sflight_3)
  FROM sflight
  WHERE seatsmax_b > 22.

lt_rng_carrid = VALUE #(
  FOR GROUPS carrid OF ls_sflight IN lt_sflight_3
  GROUP BY ls_sflight-carrid
  ( sign   = 'I'
    option = 'EQ'
    low    = carrid )
).

IF lt_rng_carrid IS NOT INITIAL.
  SELECT carrid, carrname, currcode
    INTO TABLE @DATA(lt_scarr)
    FROM scarr
    WHERE carrid IN @lt_rng_carrid.
ENDIF.

DATA lt_rng_carrid TYPE RANGE OF s_carr_id.

SELECT carrid, connid, fldate, price

INTO TABLE @DATA(lt_sflight_3)

FROM sflight

WHERE seatsmax_b > 22.

lt_rng_carrid = VALUE #(

FOR GROUPS carrid OF ls_sflight IN lt_sflight_3

GROUP BY ls_sflight-carrid

( sign = 'I'

option = 'EQ'

low = carrid )

IF lt_rng_carrid IS NOT INITIAL.

SELECT carrid, carrname, currcode

INTO TABLE @DATA(lt_scarr)

FROM scarr

WHERE carrid IN @lt_rng_carrid.

ENDIF.

A range table is not always a replacement for FOR ALL ENTRIES. It works best when you have one field in the condition. If you need several fields as one key combination, FOR ALL ENTRIES or JOIN can be more suitable.

Replace FOR ALL ENTRIES with JOIN

Very often, a JOIN is better than FOR ALL ENTRIES. If both tables are database tables and the relationship is clear, a JOIN lets the database do the work in one SELECT statement.

SELECT sflight~carrid,
       sflight~connid,
       sflight~fldate,
       sflight~price,
       scarr~carrname,
       scarr~currcode
  INTO TABLE @DATA(lt_result)
  FROM sflight
  INNER JOIN scarr
    ON scarr~carrid = sflight~carrid
  WHERE sflight~seatsmax_b > 22.

SELECT sflight~carrid,

sflight~connid,

sflight~fldate,

sflight~price,

scarr~carrname,

scarr~currcode

INTO TABLE @DATA(lt_result)

FROM sflight

INNER JOIN scarr

ON scarr~carrid = sflight~carrid

WHERE sflight~seatsmax_b > 22.

Use JOIN when the data can be selected directly from related database tables. Use FOR ALL ENTRIES when the driving values are already calculated or collected in an internal table and cannot be expressed easily as a database join.

FOR ALL ENTRIES checklist

Before using SELECT FOR ALL ENTRIES in ABAP, check these points:

The internal table after FOR ALL ENTRIES is checked with IS NOT INITIAL.
The internal table contains only necessary key fields if possible.
Duplicate key combinations are removed or avoided.
The WHERE condition uses meaningful key comparisons.
A JOIN or range table was considered as an alternative.
Only required fields are selected from the database.

When should you use FOR ALL ENTRIES?

FOR ALL ENTRIES is still useful in ABAP development when the input values are already stored in an internal table. It is also useful when the selection logic is built in ABAP before the database read.

However, it should not be the first automatic choice for every SELECT. Ckeck if a JOIN is simple and readable, use JOIN. If only one field is needed, consider a range table. If the driving table is huge, reduce it before the SELECT.

FAQ: FOR ALL ENTRIES in SELECT ABAP

Is FOR ALL ENTRIES new ABAP syntax? No. FOR ALL ENTRIES is not new ABAP syntax. It is an Open SQL technique that has been used for many years, but it is still important.

What happens if the internal table is empty? The selection can return many more rows than expected. Always check the internal table with IS NOT INITIAL before SELECT FOR ALL ENTRIES.

Is JOIN better than FOR ALL ENTRIES? Often yes, especially when both data sources are database tables and the relationship can be expressed directly in SQL. FOR ALL ENTRIES is useful when the driving values already exist in an internal table.

Can I replace FOR ALL ENTRIES with a range table? Yes, in simple cases where the condition uses one field. For multi-field key combinations, a range table is usually not enough.

FOR ALL ENTRIES in SELECT ABAP is a useful tool, but it must be used carefully. The most important rules are to check that the internal table is not empty, reduce duplicate keys, select only necessary fields, and compare FOR ALL ENTRIES with alternatives such as JOIN or range tables. Used correctly, it remains a practical technique for database selection in SAP ABAP.

Blog ABAP SAP from Artur

FOR ALL ENTRIES in SELECT ABAP: Examples, Checks and Alternatives

What is FOR ALL ENTRIES in ABAP?

Simple FOR ALL ENTRIES example

Always check that the internal table is not empty

Remove duplicate key values before FOR ALL ENTRIES

Replace FOR ALL ENTRIES with a range table

Replace FOR ALL ENTRIES with JOIN

FOR ALL ENTRIES checklist

When should you use FOR ALL ENTRIES?

FAQ: FOR ALL ENTRIES in SELECT ABAP

Leave a Reply Cancel reply

ABAP programming with Artur is a collection of articles and videos about the ABAP programming language and working in the SAP system. There are both abap lessons for beginners as well as new syntax in ABAP and SAP training for SAP consultants.