ABAP SQL Basics

ABAP SQL (called *Open SQL* before 7.53) is database-independent SQL embedded in ABAP.

ABAP SQL (called *Open SQL* before 7.53) is database-independent SQL embedded in ABAP. It handles client filtering, uses the table buffer, and is checked at compile time.

14.1 Strict (modern) syntax

Since 7.40 SP05, host variables are escaped with @, field lists are comma-separated, and INTO can come at the end (7.40 SP08). Mixing old and new syntax in one statement is not allowed.

" Modern
SELECT carrid, connid, cityfrom, cityto
  FROM spfli
  WHERE carrid = @lv_carrid
  ORDER BY connid
  INTO TABLE @DATA(lt_connections).

" Classic equivalent
DATA lt_spfli TYPE TABLE OF spfli.
SELECT carrid connid cityfrom cityto
  FROM spfli
  INTO CORRESPONDING FIELDS OF TABLE lt_spfli
  WHERE carrid = lv_carrid.

14.2 SELECT variants

After a SELECT: sy-subrc = 0 if rows found (4 otherwise), sy-dbcnt = number of rows.

" Single row by full key
SELECT SINGLE * FROM scarr WHERE carrid = 'LH' INTO @DATA(ls_carrier).
IF sy-subrc <> 0. " not found
ENDIF.

" Single field
SELECT SINGLE carrname FROM scarr WHERE carrid = 'LH' INTO @DATA(lv_name).

" Existence check (7.40 SP08+ idiom)
SELECT SINGLE @abap_true FROM scarr WHERE carrid = 'LH' INTO @DATA(lv_exists).

" Limit rows
SELECT * FROM sflight ORDER BY price DESCENDING INTO TABLE @DATA(lt_top10) UP TO 10 ROWS.

" Append to an existing table
SELECT * FROM sflight WHERE carrid = 'AA' APPENDING TABLE @lt_flights.

" Loop (avoid for large data: one round trip per package)
SELECT carrid, connid FROM spfli INTO @DATA(ls_conn).
  " processed row by row
ENDSELECT.

" Package processing for huge volumes
SELECT * FROM sflight INTO TABLE @DATA(lt_pkg) PACKAGE SIZE 1000.
  " process 1000 rows at a time
ENDSELECT.

14.3 WHERE conditions

Empty ranges match everything — powerful for optional selection-screen fields.

SELECT * FROM sflight
  WHERE carrid IN @s_carrid                     " select-option / range
    AND fldate BETWEEN @lv_from AND @lv_to
    AND price > 500
    AND planetype LIKE 'A3%'
    AND connid IN ('0017', '0064')
    AND seatsocc IS NOT NULL
  INTO TABLE @DATA(lt_result).

14.4 FOR ALL ENTRIES

Reads rows matching entries of an internal table (classic pattern before joins/CDS were widespread):

Duplicates are removed automatically; prefer joins or CDS views on HANA.

IF lt_orders IS NOT INITIAL.                  " CRITICAL: empty table selects ALL rows
  SELECT kunnr, name1 FROM kna1
    FOR ALL ENTRIES IN @lt_orders
    WHERE kunnr = @lt_orders-customer
    INTO TABLE @DATA(lt_customers).
ENDIF.

14.5 Ordering and distinct

SELECT DISTINCT carrid FROM spfli ORDER BY carrid INTO TABLE @DATA(lt_carriers).
SELECT * FROM scarr ORDER BY PRIMARY KEY INTO TABLE @DATA(lt_all).
SELECT * FROM sflight ORDER BY price DESCENDING, fldate ASCENDING INTO TABLE @DATA(lt_sorted).

14.6 SQL expressions in the field list (7.50+)

SELECT carrid, connid,
       price * 2                                   AS double_price,
       CASE WHEN seatsocc > 300 THEN 'FULL' ELSE 'OK' END AS load_status,
       concat( carrid, connid )                    AS flight_key,
       coalesce( planetype, 'UNKNOWN' )            AS plane,
       CAST( seatsmax AS CHAR( 10 ) )               AS seats_text,
       @lv_factor                                  AS factor,
       dats_days_between( fldate, @sy-datum )      AS days_ago
  FROM sflight
  INTO TABLE @DATA(lt_expr).