Open SQL Part 10: Open SQL in Real Reports and Interfaces

📌 KEY POINTS
✅ Real programs follow one flow: selection → authorization → read (few statements) → process in memory → output/update.
✅ Changing data: INSERT/UPDATE/MODIFY … FROM TABLE for mass changes, with lock objects and COMMIT WORK.
✅ Custom tables only — standard SAP data is changed via BAPIs/APIs, never directly.
💡 Review every program with the checklist below before transport.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–9 covered the tools. This final part builds a realistic flight occupancy report and a booking extract for an external system.

━━━━━━━━━━━━━━━━━━━━
▶ 1. THE READ FLOW

• Selection screen with ranges and validation (ABAP Core Part 5).
• AUTHORITY-CHECK on organizational values (airline) before reading.
• Read header data with field lists and WHERE conditions.
• Read dependent data once: JOIN or FAE (with emptiness check and de-duplication).
• Lookup tables (master data) into HASHED tables.
• Process in memory with sorted/hashed tables — no SQL in loops.
• Output via ALV (ALV topic) or file.

━━━━━━━━━━━━━━━━━━━━
▶ 2. WRITING DATA — CUSTOM TABLES ONLY

• INSERT ztab FROM TABLE gt_new. — mass insert; duplicates raise an exception or sy-subrc 4 (with ACCEPTING DUPLICATE KEYS).
• UPDATE ztab FROM TABLE gt_changed. — by primary key.
• MODIFY ztab FROM TABLE gt_rows. — insert or update.
• DELETE ztab FROM TABLE gt_del. / DELETE FROM ztab WHERE ….
• Protect concurrent changes with lock objects (ENQUEUE_/DEQUEUE_ function modules generated in SE11).
• COMMIT WORK at a consistent point; ROLLBACK WORK on error.

⚠️ Never update SAP standard tables directly — use BAPIs or released APIs (BAPI topic).

━━━━━━━━━━━━━━━━━━━━
▶ 3. INTERFACE EXTRACTS

• Delta selection by change date/time stamp.
• Package processing for large volumes (Part 7).
• Conversion exits (ALPHA) and currency/unit formatting for external formats.
• Log what was extracted (counts, keys) in the application log.

━━━━━━━━━━━━━━━━━━━━
▶ 4. REVIEW CHECKLIST
□ No SELECT * without reason □ All filters in WHERE □ No SELECT in loops □ FAE: not empty, de-duplicated, full keys □ SELECT SINGLE only with full key □ Aggregation in SQL □ Currency grouped □ Hashed/sorted lookup tables □ Package processing for mass data □ Authorization checks □ Locks + commit handling for updates □ ST05 trace done with realistic data.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: occupancy report with totals, then the extract writing into a custom status table ZFLIGHT_EXTR (create it in SE11 with key MANDT, CARRID, CONNID, FLDATE and fields EXTRACTED_ON, BOOKINGS).

✅ CHECKPOINT: you can build and review a complete SQL-based program. Open SQL complete — next topic: Modularization.

A new team member built an interface extract that selected headers, then items in a loop, then updated a custom log table with one UPDATE per item and a COMMIT after each. It worked in development and took four hours in production, creating thousands of tiny database transactions.
Using the review checklist, the team rewrote it as a join with aggregation, FAE for the external key list, a hashed master-data table, and one MODIFY FROM TABLE per package with a commit per package. Runtime dropped to eight minutes; the checklist became part of the team's definition of done.

• SQL inside loops for reads or writes.
• Direct updates of SAP standard tables.
• One COMMIT per row or no commit strategy at all.
• No authorization check for selected organizational values.
• No lock handling for custom tables changed by several users/jobs.

• Selection → authorization → bulk read → memory processing → output/mass update.
• Mass DML with FROM TABLE; commit at consistent points.
• Lock objects for custom tables with concurrent changes.
• BAPIs/APIs for SAP standard data.
• Apply the SQL review checklist and trace before transport.

Questions: "How do you structure a performant report?", "How do you update custom tables efficiently?", "May you update SAP standard tables directly?"
Strong answer: select with filters and field lists after authorization checks, read related data in bulk (JOIN/FAE), use hashed lookup tables, process in memory, output via ALV; update custom tables with INSERT/UPDATE/MODIFY … FROM TABLE, lock objects and COMMIT/ROLLBACK at consistent points; never modify SAP standard tables directly — use BAPIs/released APIs.