ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → WITH
WITH subquery_clauses
Other versions:
7.31 | 7.40 | 7.54
Syntax
... { 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] ...
Effect
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.
Notes
-
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 BYclauses 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 ACCESSin theFROMclause, 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 theSELECTstatement, 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.
Example
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.
...
WITH
+flights AS ( SELECT FROM sflight
FIELDS carrid,
connid,
AVG( seatsocc AS DEC( 16,2 ) ) AS avg
WHERE carrid IN @carrids
GROUP BY carrid, connid
ORDER BY carrid, connid UP TO 1 ROWS )
##db_feature_mode[limit_in_subselect_or_cte]
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).
Example
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 ).
DATA:
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 )`.
WITH
+cities AS (
SELECT (sel_sub1)
FROM (frm_sub)
WHERE (whr_sub)
UNION DISTINCT
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> ).