Open SQL Part 4: SELECT SINGLE vs UP TO 1 ROWS

📌 KEY POINTS
✅ SELECT SINGLE — use when you specify the complete primary key: one unique row.
✅ UP TO 1 ROWS with ORDER BY — use when several rows match and you want a defined one (latest, cheapest).
⚠️ SELECT SINGLE with an incomplete key returns an arbitrary matching row — not "the first".
💡 For an existence check select one small field (or a constant) — no need to read the whole row.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–3 read sets of rows. Very often you need one row: the airline name, a customer, the latest booking.

━━━━━━━━━━━━━━━━━━━━
▶ 1. SELECT SINGLE
SELECT SINGLE carrname FROM scarr INTO lv_name WHERE carrid = p_carr.

• Complete primary key in WHERE → exactly one row or none.
• Uses the table buffer for buffered tables when the full key is specified.
• sy-subrc 0 found / 4 not found.

━━━━━━━━━━━━━━━━━━━━
▶ 2. INCOMPLETE KEY — THE PROBLEM
SELECT SINGLE * FROM sflight INTO gs_flight WHERE carrid = 'LH'.
Many rows match; the database returns any one of them. Code that assumes "the first flight" works by chance. Code inspector/ATC warns about SELECT SINGLE without full key.

━━━━━━━━━━━━━━━━━━━━
▶ 3. UP TO 1 ROWS + ORDER BY
SELECT fldate price FROM sflight INTO (lv_date, lv_price) UP TO 1 ROWS
WHERE carrid = 'LH' AND connid = '0400' ORDER BY fldate DESCENDING.

ENDSELECT.

(classic form needs ENDSELECT when INTO a work area; in strict syntax you can select INTO a work area with UP TO 1 ROWS without ENDSELECT in newer releases, or INTO TABLE and take the first row).

• Defined result: the latest flight.

━━━━━━━━━━━━━━━━━━━━
▶ 4. EXISTENCE CHECKS

• SELECT SINGLE @abap_true FROM sbook INTO @DATA(lv_exists) WHERE customid = @lv_cust. (7.40+)
• Classic: SELECT SINGLE customid FROM sbook INTO lv_cust WHERE customid = lv_cust. — select one small field.
• Or SELECT COUNT(*) … UP TO 1 ROWS.

━━━━━━━━━━━━━━━━━━━━
▶ 5. DON'T SELECT IN LOOPS
SELECT SINGLE inside a LOOP over thousands of rows means thousands of database round trips. Read all needed rows once (FOR ALL ENTRIES, JOIN) into a hashed table and READ TABLE in the loop — except for buffered tables, where SELECT SINGLE by full key is cheap.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: SELECT SINGLE by full key, the arbitrary row with an incomplete key, the latest flight via ORDER BY + UP TO 1 ROWS, and an existence check.

✅ CHECKPOINT: every single-row read in your code returns a defined row.

A pricing program read "the current price" with SELECT SINGLE on a condition table using only material and customer, without the validity date. On one database the returned row happened to be the newest; after the HANA migration, a different (older) row came back, and invoices were priced wrongly for two days.
The fix used ORDER BY validity DESCENDING with UP TO 1 ROWS and a date condition, which returns a defined row on every database.

• SELECT SINGLE with incomplete keys expecting "the first" row.
• Reading entire rows for existence checks.
• SELECT SINGLE inside loops on non-buffered tables.
• Forgetting ENDSELECT with UP TO 1 ROWS into a work area (classic).
• Not checking sy-subrc.

• SELECT SINGLE only with the full primary key.
• UP TO 1 ROWS + ORDER BY for defined single rows.
• Minimal field lists for existence checks.
• Bulk reads + hashed tables instead of SELECT in loops.
• Check sy-subrc after every single read.

Questions: "SELECT SINGLE vs UP TO 1 ROWS?", "What happens with SELECT SINGLE and an incomplete key?", "How do you check existence efficiently?"
Strong answer: SELECT SINGLE is for reads with the complete primary key (unique row, buffer-capable); with incomplete keys any matching row is returned; UP TO 1 ROWS combined with ORDER BY returns a defined row such as the latest; existence checks select a minimal field or a constant; avoid SELECT SINGLE in loops except for buffered tables.