ABAP Developmentbeginner

Open SQL Part 1: How ABAP Reads the Database

Key takeaway

Understand Open SQL (ABAP SQL): database-independent access to dictionary tables, client handling, the SELECT forms you will meet in classic code, and the classic vs new strict syntax.

What this complete series covers

Learn Open SQL in a clear, practical sequence — from the basic idea to real project use, troubleshooting, and interview-ready understanding.

Who it is for: Best for SAP consultants, developers, and learners who want a practical understanding of Open SQL.
Prerequisites: Basic SAP navigation and ABAP syntax help. For hands-on practice, use an ABAP system with ADT or the relevant ABAP development tools.
Final outcome: By the end, you should be able to explain Open SQL clearly, follow the main end-to-end flow, apply it in a project scenario, and troubleshoot the most common issues.

Part 1: Open SQL Part 1: How ABAP Reads the Database
Improve Open SQL performance and maintainability by selecting only needed fields and filtering at the database, avoiding SELECT and application-side filtering for large business datasets.
Part 2: Open SQL Part 2: SELECT Basics — Field Lists, WHERE and INTO
Golden rules for Open SQL performance: field lists, WHERE, joins vs FOR ALL ENTRIES, package processing, and HANA code pushdown.
Part 3: Open SQL Part 3: FOR ALL ENTRIES — Correct Use
Use FOR ALL ENTRIES safely only with a nonempty, deduplicated driver set and compare it with JOIN/CDS alternatives based on semantics and measured SQL rather than habit.
Part 4: Open SQL Part 4: SELECT SINGLE vs UP TO 1 ROWS
Use SELECT SINGLE when the predicate identifies one logical record, and use an ordered UP TO 1 ROWS query when the requirement is to choose one row from several possible matches.
Part 5: Open SQL Part 5: JOINs — When to Use and When to Avoid
Use Open SQL joins when table relationships are clear and set-based retrieval improves clarity, but control one-to-many cardinality, filtering and semantics so joins do not multipl
Part 6: Open SQL Part 6: Aggregates, GROUP BY and HAVING
Perform totals and grouped calculations in Open SQL with aggregate functions, GROUP BY and HAVING when the database can return the exact summarized result instead of transferring detail rows to ABAP.
Part 7: Open SQL Part 7: PACKAGE SIZE and Large Data Processing
Use Open SQL PACKAGE SIZE to stream large result sets through bounded internal-table packages when a cursor-style read fits the problem, while keeping processing restartable and bu
Part 8: Open SQL Part 8: Performance Analysis with ST05, SAT and SQL Monitor
Tune Open SQL from evidence by using ST05 for statement-level trace, SAT for ABAP runtime distribution and SQL Monitor for aggregated productive SQL usage before changing code or indexes.
Part 9: Open SQL Part 9: HANA-Friendly SQL and Code Pushdown
Write HANA-friendly ABAP by pushing selective filtering, joins and aggregation to the database while keeping business logic readable and respecting S/4HANA data-model and authorization semantics.
Part 10: Open SQL Part 10: Open SQL in Real Reports and Interfaces
Design Open SQL differently for reports, interfaces and APIs by matching result size, latency, paging and transaction context instead of reusing one unrestricted SELECT pattern everywhere.

System and release assumptions: Examples follow modern ABAP practices. Exact syntax, available APIs, and tools can vary by SAP release, so release-dependent points are called out where needed.

Explanation

📌 KEY POINTS
✅ Open SQL (now called ABAP SQL) is ABAP's database-independent SQL for dictionary tables and views.
✅ The client (MANDT) is handled automatically — you never write it in WHERE.
✅ Classic syntax (space-separated fields, no @) still works; new strict syntax uses commas and @host variables.
⚠️ Native SQL / direct DB access bypasses client handling and buffering — avoid it in application code.

━━━━━━━━━━━━━━━━━━━━
▶ WHAT THIS SERIES COVERS

Part 1 — how ABAP reads the database (this lesson)
Part 2 — SELECT basics: field lists, WHERE, INTO
Part 3 — FOR ALL ENTRIES
Part 4 — SELECT SINGLE vs UP TO 1 ROWS
Part 5 — JOINs
Part 6 — aggregates, GROUP BY, HAVING
Part 7 — PACKAGE SIZE and large data
Part 8 — performance analysis with ST05, SAT, SQL Monitor
Part 9 — HANA-friendly SQL and code pushdown
Part 10 — Open SQL in real reports and interfaces

Running example: SCARR, SPFLI, SFLIGHT, SBOOK, SCUSTOM.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHAT OPEN SQL DOES
You write SQL in ABAP; the database interface translates it into the SQL of the actual database (HANA, Oracle, DB2 …). Your program works on every supported database.

━━━━━━━━━━━━━━━━━━━━
▶ 2. AUTOMATIC CLIENT HANDLING
Client-dependent tables have MANDT as first key field. Open SQL adds "MANDT = sy-mandt" automatically. CLIENT SPECIFIED (classic) / USING CLIENT (new) exists for rare cross-client tools only.

━━━━━━━━━━━━━━━━━━━━
▶ 3. TABLE BUFFERING
Tables with buffering in SE11 (e.g. small customizing tables) are read from the application server buffer — very fast. Some statements bypass the buffer (aggregates, JOINs, BYPASSING BUFFER, certain WHERE conditions).

━━━━━━━━━━━━━━━━━━━━
▶ 4. SELECT FORMS YOU WILL SEE

SELECT SINGLE … INTO wa WHERE full key. — one row.
SELECT … INTO TABLE itab WHERE … — many rows into a table (preferred).
SELECT … INTO wa … ENDSELECT. — loop over rows one by one (classic; usually slower).
SELECT … FOR ALL ENTRIES IN itab — driver-table based reads (Part 3).

━━━━━━━━━━━━━━━━━━━━
▶ 5. CLASSIC VS STRICT SYNTAX
Classic: SELECT carrid connid FROM sflight INTO TABLE gt_flight WHERE carrid = p_carr.
Strict (7.40 SP05+): SELECT carrid, connid FROM sflight WHERE carrid = @p_carr INTO TABLE @gt_flight.
Rules of strict mode: comma-separated field list, @ for ABAP variables, INTO at the end (recommended). You cannot mix styles in one statement. Both are valid in classic systems; new syntax enables inline declarations and SQL expressions.

━━━━━━━━━━━━━━━━━━━━
▶ 6. SY-SUBRC AND SY-DBCNT
sy-subrc = 0 rows found, 4 none; sy-dbcnt = number of rows.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: the same selection in classic and strict syntax, SELECT … ENDSELECT vs INTO TABLE, and sy-dbcnt.

✅ CHECKPOINT: you can read any Open SQL statement in classic or new syntax.

Code example

ABAP Code

REPORT zosql_part1. DATA: gt_flight TYPE STANDARD TABLE OF sflight,      gs_flight TYPE sflight,      gs_carr   TYPE scarr,      gv_lines  TYPE i. PARAMETERS p_carr TYPE s_carr_id DEFAULT 'LH'. START-OF-SELECTION.* ===== 1. SELECT ... INTO TABLE: all rows in one round trip =====  SELECT * FROM sflight    INTO TABLE gt_flight    WHERE carrid = p_carr.                     " MANDT added automatically  DESCRIBE TABLE gt_flight LINES gv_lines.  WRITE: / 'INTO TABLE, rows:', gv_lines, 'sy-dbcnt:', sy-dbcnt. * ===== 2. SELECT ... ENDSELECT: row by row (avoid for large volumes) =====  SELECT * FROM sflight INTO gs_flight    UP TO 5 ROWS    WHERE carrid = p_carr.    WRITE: / gs_flight-connid, gs_flight-fldate.  ENDSELECT. * ===== 3. SELECT SINGLE with full key =====  SELECT SINGLE * FROM scarr INTO gs_carr    WHERE carrid = p_carr.  IF sy-subrc = 0.    WRITE: / 'airline:', gs_carr-carrname.  ENDIF. * NEVER in application code:* EXEC SQL. SELECT ... FROM SFLIGHT ... ENDEXEC.   -> no client handling, no portability * 💡 Modern (strict) syntax, 7.40 SP05+: commas in field lists, @ for host variables,*    INTO at the end and inline targets:*    SELECT carrid, connid, fldate FROM sflight WHERE carrid = @p_carr*      INTO TABLE @DATA(lt_flights).*    Classic and strict syntax cannot be mixed within one statement.

Real project scenario

A custom report used native SQL (EXEC SQL) "for speed" to read an SAP table. After a system copy into a multi-client test system, the report showed data from all clients, including a training client with fictitious amounts, because native SQL does not add the client condition.
Rewriting it in Open SQL restored automatic client handling, allowed table buffering and made the report database-independent for the planned HANA migration.

Common mistakes

Using native SQL in application code.
Writing MANDT into WHERE conditions unnecessarily.
Mixing classic and strict syntax in one statement.
SELECT … ENDSELECT for large result sets.
Ignoring sy-subrc after SELECT.

Best practices

Use Open SQL for all application database access.
Prefer INTO TABLE over SELECT … ENDSELECT.
Use strict syntax in new code where the release allows.
Let Open SQL handle the client.
Check sy-subrc / sy-dbcnt.

Interview angle

Questions: "What is Open SQL?", "How is the client handled?", "Classic vs new SQL syntax?"
Strong answer: Open SQL/ABAP SQL is the database-independent SQL layer for dictionary objects; the client is added automatically; buffering applies to buffered tables; classic syntax uses space-separated fields without @, strict syntax uses commas, @ host variables and allows expressions and inline declarations; avoid native SQL in application code.

Practice this lesson

Quick check

1. Which statement about Open SQL client handling is correct?

2. Best practice for large result sets in ABAP is to:

3. Which is the recommended replacement for SELECT inside LOOP?

Interview questions

1. What is the impact of client handling in Open SQL and how do you deal with cross-client queries safely?

Answer it in your own words first, then compare.

2. Describe how client handling (MANDT) works in Open SQL. When is client filtering automatic, and when must it be handled explicitly?