Open SQL pitfalls
What you'll learn
FOR ALL ENTRIES on an empty table returns EVERYTHING, and SELECT SINGLE without the full key returns a random row — guard the driver and qualify the key.
- SELECT SINGLE without the full key returns an undefined row — fully qualify the key or add ORDER BY.
- FOR ALL ENTRIES on an empty driver returns ALL rows — always guard with `if lines( itab ) > 0`.
- FOR ALL ENTRIES silently de-duplicates the driver; it can never deliver 1:1 cardinality — use a JOIN.
If you have debugged one of these in production, you already understand it better than most references can teach — this catalog names the pattern and its remedy precisely, so the next developer doesn't pay the same tuition. The two Open SQL pitfalls that cause production incidents are both about *absence*. A SELECT SINGLE without a fully-qualified primary key returns *an* arbitrary row — on HANA the row the optimizer happens to find first, which differs from the AnyDB era and differs run to run. And FOR ALL ENTRIES over an *empty* driver table does not return zero rows; it strips the IN-list condition entirely and returns the *whole* target table. The fix is a one-line guard: `if lines( itab ) > 0`.
FOR ALL ENTRIES has a second silent behaviour: it de-duplicates the driver before building the read, so it can never give you 1:1 cardinality. If you needed one result row per driver row you have already lost rows before the database is touched — you must use a JOIN instead. These behaviours predate HANA but are more damaging now because result-set sizes and row order changed under columnar storage.
The rest of the family is about cost you cannot see in the syntax. INTO CORRESPONDING FIELDS OF TABLE is slower than a named field list and obscures the real types (a field that silently doesn't map is a defect, not an error). ORDER BY PRIMARY KEY is not free on HANA — there is no implicit sort, so only ask for ordering you actually consume. And SELECT DISTINCT carries a real de-duplication cost that, for input that is already mostly unique, can be dearer than reading into a SORTED table.
Key points
- SELECT SINGLE without the full key returns an undefined row — fully qualify the key or add ORDER BY.
- FOR ALL ENTRIES on an empty driver returns ALL rows — always guard with `if lines( itab ) > 0`.
- FOR ALL ENTRIES silently de-duplicates the driver; it can never deliver 1:1 cardinality — use a JOIN.
- INTO CORRESPONDING is slow and hides type mismatches; ORDER BY PRIMARY KEY and DISTINCT are not free.
Examples
If lt_orders is ever empty, this reads every row of zorder_item — the driver condition disappears entirely.
ABAPselect * from zorder_item
for all entries in @lt_orders
where order_id = @lt_orders-order_id
into table @data(lt_items).The guard short-circuits the empty-driver case; for true 1:1 cardinality, reach for a JOIN instead of FAE.
ABAPif lines( lt_orders ) > 0.
select order_id, item_no, line_total
from zorder_item
for all entries in @lt_orders
where order_id = @lt_orders-order_id
into table @data(lt_items).
endif.Source notes: clean-core-curriculum §10.1
Ask Claude
Build a prompt from this lesson + your question and open a fresh Claude chat with it pre-filled — handy for adapting a before/after pattern to your own object.