ABAP Developmentintermediate

Open SQL Part 3: FOR ALL ENTRIES — Correct Use

Key takeaway

Use FOR ALL ENTRIES safely: the empty-driver trap, duplicate removal, automatic duplicate elimination in the result, key fields in the field list, package splitting, and when a JOIN is better.

Explanation

📌 KEY POINTS
✅ FOR ALL ENTRIES (FAE) reads rows matching values from an internal "driver" table.
⚠️ If the driver table is EMPTY, the WHERE condition is ignored and the WHOLE table is read.
✅ FAE removes duplicate result rows — include all key fields in the field list, or rows will be lost.
💡 De-duplicate the driver first; consider a JOIN when both tables are in the database.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 2 covered single-table SELECTs. Classic reports often read headers first, then related data for those headers — with FOR ALL ENTRIES.

━━━━━━━━━━━━━━━━━━━━
▶ 1. THE PATTERN
SELECT carrid connid fldate … FROM sflight INTO TABLE gt_flight WHERE carrid = p_carr.
SELECT bookid customid … FROM sbook INTO TABLE gt_book
FOR ALL ENTRIES IN gt_flight
WHERE carrid = gt_flight-carrid AND connid = gt_flight-connid AND fldate = gt_flight-fldate.
The database interface turns the driver rows into IN lists or OR conditions and executes several statements (package-wise).

━━━━━━━━━━━━━━━━━━━━
▶ 2. RULE 1 — CHECK THE DRIVER IS NOT EMPTY
IF gt_flight IS NOT INITIAL. (or CHECK gt_flight[] IS NOT INITIAL) before the FAE select.
With an empty driver, the whole FAE WHERE part is ignored → full table read (millions of bookings).

━━━━━━━━━━━━━━━━━━━━
▶ 3. RULE 2 — REMOVE DUPLICATES FROM THE DRIVER
SORT + DELETE ADJACENT DUPLICATES COMPARING the fields used in WHERE — or build a small unique key table (Internal Tables Part 7). Fewer driver rows = fewer database statements.

━━━━━━━━━━━━━━━━━━━━
▶ 4. RULE 3 — INCLUDE THE KEY FIELDS
FAE behaves like SELECT DISTINCT on the selected columns: identical result rows are merged. If you select only customid and price from SBOOK, two bookings of the same customer with the same price collapse into one → wrong totals. Always select the full primary key (carrid connid fldate bookid).

━━━━━━━━━━━━━━━━━━━━
▶ 5. OTHER LIMITATIONS

No ORDER BY in classic FAE (sort in ABAP afterwards); aggregates are restricted.
Large drivers create many statements — the profile parameters rsdb/max_blocking_factor etc. control splitting.
On HANA, a JOIN or a CDS view is often faster and simpler than FAE.

━━━━━━━━━━━━━━━━━━━━
▶ 6. FAE VS JOIN

Driver comes from the database (another table) → JOIN usually better (one statement).
Driver built in ABAP (file, calculation, user list) → FAE is the tool.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: correct FAE, the empty-driver trap (with UP TO to stay safe), and the lost-rows effect when key fields are missing.

✅ CHECKPOINT: every FAE in your code has an emptiness check, a de-duplicated driver and full keys.

Code example

ABAP Code

REPORT zosql_part3. TYPES: BEGIN OF ty_key,         carrid TYPE s_carr_id,         connid TYPE s_conn_id,         fldate TYPE s_date,       END OF ty_key,       BEGIN OF ty_join,         carrid   TYPE s_carr_id,         connid   TYPE s_conn_id,         fldate   TYPE s_date,         bookid   TYPE s_book_id,         customid TYPE s_customer,       END OF ty_join. DATA: gt_flight TYPE STANDARD TABLE OF ty_key,      gt_book   TYPE STANDARD TABLE OF sbook,      gt_empty  TYPE STANDARD TABLE OF ty_key,      gt_join   TYPE STANDARD TABLE OF ty_join,      gv_lines  TYPE i. PARAMETERS p_carr TYPE s_carr_id DEFAULT 'LH'. START-OF-SELECTION.* ----- driver table from the database -----  SELECT carrid connid fldate FROM sflight    INTO TABLE gt_flight    WHERE carrid = p_carr      AND fldate >= sy-datum. * ===== RULE 2: de-duplicate the driver =====  SORT gt_flight BY carrid connid fldate.  DELETE ADJACENT DUPLICATES FROM gt_flight COMPARING carrid connid fldate. * ===== RULE 1: never run FAE with an empty driver =====  IF gt_flight IS NOT INITIAL.* ===== RULE 3: full primary key in the field list =====    SELECT carrid connid fldate bookid customid forcuram forcurkey      FROM sbook      INTO CORRESPONDING FIELDS OF TABLE gt_book      FOR ALL ENTRIES IN gt_flight      WHERE carrid = gt_flight-carrid        AND connid = gt_flight-connid        AND fldate = gt_flight-fldate        AND cancelled = space.  ENDIF.  DESCRIBE TABLE gt_book LINES gv_lines.  WRITE: / 'bookings read:', gv_lines. * ===== THE TRAP: empty driver = no restriction (do NOT run on a big table) =====*  SELECT carrid connid fldate bookid FROM sbook*    INTO CORRESPONDING FIELDS OF TABLE gt_book*    FOR ALL ENTRIES IN gt_empty                 " gt_empty has no rows*    WHERE carrid = gt_empty-carrid.*  -> the FAE WHERE part is ignored: ALL rows of SBOOK are read. * ===== Often better: one JOIN instead of FAE =====  SELECT b~carrid b~connid b~fldate b~bookid b~customid    FROM sflight AS f    INNER JOIN sbook AS b ON b~carrid = f~carrid                         AND b~connid = f~connid                         AND b~fldate = f~fldate    INTO TABLE gt_join    WHERE f~carrid = p_carr      AND f~fldate >= sy-datum      AND b~cancelled = space.

Real project scenario

A nightly credit check selected open orders for customers listed in a file and then read items with FOR ALL ENTRIES. One night the file was empty; the program read all 40 million order items, ran out of memory and blocked the batch chain until morning.
The fix added an emptiness check before every FAE in the program. In a second finding from the same review, a missing booking key in the field list had caused duplicate amounts to collapse — totals had been understated for months.

Common mistakes

FAE without checking that the driver table is filled.
Driver table with duplicates.
Missing key fields in the field list (rows merged).
Using FAE where a JOIN would be simpler.
Expecting ORDER BY or aggregates to work as in normal SELECTs.

Best practices

Always check IS NOT INITIAL before FAE.
De-duplicate the driver on the WHERE fields.
Select the full primary key.
Prefer JOINs/CDS when data comes from the database.
Sort the result in ABAP if order matters.

Interview angle

Questions: "What happens if the FOR ALL ENTRIES table is empty?", "Why include key fields?", "FAE vs JOIN?"
Strong answer: an empty driver makes the database interface ignore the FAE condition and read all rows — always check it; FAE eliminates duplicate result rows, so select the complete key to avoid losing rows; de-duplicate the driver; prefer a JOIN or CDS view when both tables are in the database, FAE when the driver is built in ABAP.

Practice this lesson

Quick check

1. What happens when the FOR ALL ENTRIES driver table is empty?

2. Which clause is NOT allowed with FOR ALL ENTRIES?

3. What must you check before using FOR ALL ENTRIES?

Interview questions

1. What are the rules for using FOR ALL ENTRIES safely?

Answer it in your own words first, then compare.

2. Your SELECT statement uses FOR ALL ENTRIES but the driver table is sometimes empty during execution. How does this affect the query result, and how would you code defensively?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.