Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  WITH 

WITH subquery_clauses

Quick Reference

Other versions: 7.31 | 7.40 | 7.54


... { FROM source 
      FIELDS select_clause }
  | { select_clause
      FROM source }
      [WHERE sql_cond]
      [ GROUP BY group] [HAVING group_cond]
      [ ORDER BY [UP TO n ROWS [OFFSET o]]]
      [db_hints]  ...


Possible clauses and additions of subqueries of a WITH statement. These clauses define the results set of a common table expression.

The results set can be used as a temporary table in subsequent subqueries and in the current WITH statement as a data source data_source.

The names of the columns of the result set are defined by the SELECT of the subquery by default. However they can be overwritten in the WITH statement. If a union set is created in the subquery using UNION, the column names are determined by the SELECT list of the first SELECT statement.

If the clauses of the subquery contain dynamic tokens, the common table expression can only be used in other dynamic tokens of the WITH statement.

The addition UP TO n ROWS can only be used after ORDER BY and the addition OFFSET can only be used after UP TO n ROWS. An ORDER BY clause in a subquery is not supported by all databases. This means a syntax check warning from the extended program check can occur that can be hidden using the pragma ##db_feature_mode[limit_in_subselect_or_cte]. If this is detected at runtime on a database that does not support the pragma, a handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.


  • In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports ORDER BY clauses in subqueries. This requires the constant LIMIT_IN_SUBSELECT_OR_CTE of this class to be passed to the method in an internal table.

  • If a query is used to access a CDS entity associated with a CDS role and for which CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck or using the addition WITH PRIVILEGED ACCESS in the FROM clause, only that data is read implicitly that matches the access condition defined in the CDS role. If data cannot be read, ABAP programs cannot distinguish whether this is due to the conditions of the SELECT statement, the conditions of the CDS entity, or an associated CDS role. If the CDS database view database view of a CDS view is accessed, no access control takes place.


Subquery of a common table expression +flights with almost all possible clauses. The ORDER BY clause followed by the addition UP TO is not supported by all database, which means a syntax warning from the extended program check is raised that can be hidden here using the pragma ##db_feature_mode[limit_in_subselect_or_cte].

DATA carrids TYPE RANGE OF sflight-carrid. 


  +flights AS ( SELECT FROM sflight 
                       FIELDS carrid, 
                             AVG( seatsocc AS DEC( 16,2 ) ) AS avg 
                       WHERE carrid IN @carrids 
                       GROUP BY carrid, connid 
                       ORDER BY carrid, connid UP TO 1 ROWS ) 
  SELECT FROM +flights AS f 
           INNER JOIN scarr AS s 
             ON f~carrid = s~carrid 
         FIELDS s~carrname, f~connid, f~avg 
         ORDER BY s~carrname, f~connid 
         INTO TABLE @DATA(itab).


This example demonstrates a common table expression in which all clauses of the subquery, and the main query too, are specified as dynamic tokens. In the INTO clause, the addition NEW and the declaration operator @DATA(...) are used to declare a generic data reference variable that points to the result in an anonymous data object.

DATA carrid TYPE spfli-carrid VALUE 'LH'. 
cl_demo_input=>request( CHANGING field = carrid ). 

  sel_sub1 TYPE string VALUE `cityfrom AS city`, 
  sel_sub2 TYPE string VALUE `cityto AS city`, 
  frm_sub  TYPE string VALUE `spfli`, 
  whr_sub  TYPE string VALUE `carrid = @carrid`, 
  sel_main TYPE string VALUE `*`, 
  frm_main TYPE string VALUE `sgeocity`, 
  whr_main TYPE string VALUE `city IN ( SELECT city FROM +cities )`. 

  +cities AS ( 
    SELECT (sel_sub1) 
           FROM (frm_sub) 
           WHERE (whr_sub) 
    SELECT (sel_sub1) 
           FROM (frm_sub) 
           WHERE (whr_sub) ) 
  SELECT (sel_main) 
         FROM (frm_main) 
         WHERE (whr_main) 
         INTO TABLE NEW @DATA(result). 

ASSIGN result->* TO FIELD-SYMBOL(<fs>). 
cl_demo_output=>display( <fs> ).