Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท ABAP Basics

Open SQL: Reading the Database

Learn ABAP Open SQL: SELECT into internal tables, WHERE, JOIN, aggregates, FOR ALL ENTRIES, INSERT, UPDATE and DELETE, and code-pushdown performance rules.

SAP stores its data in a relational database (traditionally Oracle, DB2 or SQL Server, and on S/4HANA the in-memory SAP HANA database). ABAP programs access it through Open SQL, a database-independent dialect built into the language. You write the same SELECT whatever database runs underneath. This lesson shows how to read and write data and what makes a query fast or slow.

ABAP and the SAP database cannot run here, so ABAP listings are read-only. To practice the set logic, the runnable blocks use plain JavaScript stand-ins over in-memory arrays. Note that the real tables named below (MARA, VBAK, EKKO) exist in a real SAP system.

Tables and the dictionary

Tables are defined in SE11 (the ABAP Dictionary) with fields, data elements, a primary key and a client field MANDT. Open SQL adds the client automatically (an implicit client handling), so you rarely write MANDT yourself. Look at table contents with SE16N (data browser). Frequently met tables:

Table Content
MARA Material master, general data
KNA1 / LFA1 Customer / vendor general data
VBAK, VBAP Sales order header, items
EKKO, EKPO Purchase order header, items
BKPF, BSEG Accounting document header, items (BSEG is ACDOCA in S/4HANA)

Basic SELECT

TEXT
SELECT matnr, mtart, matkl
  FROM mara
  WHERE mtart = 'FERT'
  INTO TABLE @DATA(lt_materials)
  UP TO 100 ROWS.

IF sy-subrc <> 0.
  WRITE / 'No materials found'.
ENDIF.

The modern form (ABAP 7.40 SP05 and later) uses an @ before host variables and puts INTO at the end. SELECT SINGLE reads one row (use the full key for a unique result), and SELECT ... ENDSELECT reads in a loop but is slower than INTO TABLE.

Where, order and aggregates

TEXT
SELECT vkorg, COUNT( * ) AS orders, SUM( netwr ) AS total
  FROM vbak
  WHERE erdat >= '20260101'
  GROUP BY vkorg
  HAVING SUM( netwr ) > 10000
  ORDER BY total DESCENDING
  INTO TABLE @DATA(lt_summary).

Aggregates COUNT, SUM, AVG, MIN, MAX run in the database, which is far faster than loading all rows and adding them up in ABAP.

Joins

TEXT
SELECT k~vbeln, k~kunnr, p~posnr, p~matnr, p~kwmeng
  FROM vbak AS k
  INNER JOIN vbap AS p ON p~vbeln = k~vbeln
  WHERE k~erdat = @sy-datum
  INTO TABLE @DATA(lt_orders).

INNER JOIN keeps matches only, LEFT OUTER JOIN keeps all left rows even without a match. Join on key fields and an index if you can: the database then reads only the needed rows. Modern ABAP also supports CDS views, covered in S/4HANA Data Models: CDS, Fiori and HANA.

FOR ALL ENTRIES

Before joins on large tables were practical, programs used FOR ALL ENTRIES to read details for a list of keys:

TEXT
IF lt_orders IS NOT INITIAL.
  SELECT matnr, maktx
    FROM makt
    FOR ALL ENTRIES IN @lt_orders
    WHERE matnr = @lt_orders-matnr
      AND spras = @sy-langu
    INTO TABLE @DATA(lt_texts).
ENDIF.

Two traps: if the driver table is empty, the WHERE condition vanishes and the whole table is read, so always check it first; and the result is made distinct, so duplicate rows can disappear unless you select the full key.

Changing data

TEXT
INSERT zorders FROM @ls_order.
UPDATE zorders SET status = 'C' WHERE order_id = @lv_id.
MODIFY zorders FROM @ls_order.      " insert or update
DELETE FROM zorders WHERE order_id = @lv_id.
COMMIT WORK.

Only change custom tables (Z...) this way. Never write directly into SAP standard tables: use the delivered BAPIs and function modules, or you bypass checks and corrupt data. A transaction ends with COMMIT WORK, or ROLLBACK WORK to discard.

Set logic, runnable

The stand-in below runs the same logic as the join and the aggregate over arrays so you can see exactly what the database does.

JS Stand-in: JOIN and GROUP BY over arrays

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The empty driver table trap

JS Stand-in: FOR ALL ENTRIES with an empty driver

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Performance rules

  1. Select only the fields you need instead of SELECT *.
  2. Filter in the database (WHERE), not afterwards in a loop.
  3. Use key fields and indexes in WHERE; check with ST05 (SQL trace) and the execution plan.
  4. Avoid SELECT inside a LOOP: one query per row is the most common cause of slow reports.
  5. Use aggregates and joins, and on HANA push calculations down with CDS or AMDP (code pushdown).
  6. Do not read a table twice if one read can be reused.

Earlier lessons showed where the data you read comes from, for example the orders in Order-to-Cash in SAP SD.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SELECT ... INTO TABLE do?

    INTO TABLE fills an internal table with all matching rows in one step.
  2. 2. What happens with FOR ALL ENTRIES if the driver table is empty?

    An empty driver table removes the condition, so always check it first.
  3. 3. Why avoid a SELECT inside a LOOP?

    Many small queries are far slower than one join or one bulk read.
  4. 4. Which is the right way to change SAP standard tables?

    Standard tables must be changed through supported interfaces so that checks and consistency are kept.

Summary

  • Open SQL is database independent and reads into internal tables with INTO TABLE @DATA(...).
  • Joins, aggregates and GROUP BY run in the database; use them.
  • FOR ALL ENTRIES needs a non-empty driver table and selects distinct rows.
  • Change only custom tables directly; use BAPIs for standard data.
  • Select needed fields, filter early and avoid queries inside loops.

Next: Modularization and Classes in ABAP