Advanced ABAP SQL

Supported: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN (7.51+).

15.1 Joins

Supported: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN (7.51+). Column selector is ~; alias~* selects all columns of one source.

SELECT s~carrid, c~carrname, s~connid, s~cityfrom, s~cityto
  FROM spfli AS s
  INNER JOIN scarr AS c ON c~carrid = s~carrid
  WHERE s~countryfr = 'DE'
  INTO TABLE @DATA(lt_join).

" Left outer join: keep carriers without connections
SELECT c~carrid, c~carrname, s~connid
  FROM scarr AS c
  LEFT OUTER JOIN spfli AS s ON s~carrid = c~carrid
  INTO TABLE @DATA(lt_left).

15.2 Aggregates and grouping

SELECT carrid,
       COUNT( * )         AS flights,
       SUM( seatsocc )    AS passengers,
       AVG( price )       AS avg_price,
       MIN( fldate )      AS first_flight,
       MAX( fldate )      AS last_flight
  FROM sflight
  GROUP BY carrid
  HAVING COUNT( * ) > 10
  ORDER BY passengers DESCENDING
  INTO TABLE @DATA(lt_stats).

15.3 Subqueries, UNION, and set operations

" Subquery
SELECT * FROM scarr
  WHERE carrid IN ( SELECT carrid FROM spfli WHERE countryto = 'US' )
  INTO TABLE @DATA(lt_us_carriers).

SELECT * FROM scarr AS c
  WHERE EXISTS ( SELECT * FROM sflight WHERE carrid = c~carrid AND price > 1000 )
  INTO TABLE @DATA(lt_premium).

" UNION (7.56: also INTERSECT and EXCEPT)
SELECT carrid AS id FROM scarr
UNION
SELECT carrid AS id FROM spfli
INTO TABLE @DATA(lt_ids).

15.4 Common table expressions (WITH, 7.51+)

WITH +revenue AS (
  SELECT carrid, SUM( paymentsum ) AS total FROM sflight GROUP BY carrid )
SELECT r~carrid, c~carrname, r~total
  FROM +revenue AS r INNER JOIN scarr AS c ON c~carrid = r~carrid
  ORDER BY r~total DESCENDING
  INTO TABLE @DATA(lt_rev).

15.5 Selecting from internal tables (7.52+) and window functions (7.54+)

SELECT carrid, connid FROM @lt_input AS itab
  WHERE connid > '0100'
  INTO TABLE @DATA(lt_filtered).

SELECT carrid, connid, fldate, price,
       ROW_NUMBER( ) OVER( PARTITION BY carrid ORDER BY price DESCENDING ) AS rank_in_carrier
  FROM sflight
  INTO TABLE @DATA(lt_ranked).

15.6 CDS-aware SQL

CDS view entities (§33) are selected like tables, including associations via path expressions and parameters:

CDS access control (DCL) is applied automatically unless WITH PRIVILEGED ACCESS is specified.

SELECT FROM zi_salesorder
  FIELDS salesorder, customer, \_customer-customername AS customer_name, grossamount
  WHERE overallstatus = 'O'
  INTO TABLE @DATA(lt_so).

SELECT * FROM zi_orders_by_date( p_date = @sy-datum ) INTO TABLE @DATA(lt_today).

15.7 Dynamic SQL tokens

Validate dynamic tokens against user input with CL_ABAP_DYN_PRG (e.g. check_table_name_str, quote) to avoid SQL injection.

DATA(lv_table)  = `SCARR`.
DATA(lv_fields) = `CARRID, CARRNAME`.
DATA(lv_where)  = |CARRID = 'LH'|.
DATA lt_any TYPE STANDARD TABLE OF scarr WITH EMPTY KEY.
SELECT (lv_fields) FROM (lv_table) WHERE (lv_where)
  INTO CORRESPONDING FIELDS OF TABLE @lt_any.

15.8 Native SQL (ADBC)

For database-specific SQL use ADBC (CL_SQL_STATEMENT) or, better, AMDP (§33). EXEC SQL … ENDEXEC is obsolete.