AS ABAP Release 758, ©Copyright 2024 SAP SE. All rights reserved.
ABAP - Keyword Documentation → ABAP - Programming Language → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Read Access →
WITH
Syntax
WITH
+cte1[( name1, name2, ... )] AS ( SELECT
subquery_clauses [UNION|INTERSECT|EXCEPT ...] ),
[hierarchy]
[associations][,
+cte2[( name1, name2, ... )] AS ( SELECT
subquery_clauses [UNION|INTERSECT|EXCEPT ...] ),
[hierarchy]
[associations],
... ]
SELECT mainquery_clauses
[UNION|INTERSECT|EXCEPT ...]
INTO|APPENDING target
[UP TO ...] [OFFSET ...]
[abap_options].
...
[ENDWITH].
Effect
The ABAP SQL keyword WITH introduces the definition of common table expressions (CTE for short) to be used in a final main query. WITH can be used as a standalone statement, as shown here, or after the statement OPEN CURSOR.
Each common table expression creates a tabular result set in a subquery, which can be used in the subsequent queries of the WITH statement as data source data_source. The WITH statement consists of the following parts:
The set operators UNION, INTERSECT, and EXCEPT can be used to combine the result sets of multiple main queries. In this case, additional rules query_clauses apply when specifying clauses.
A common table expression defined in the WITH statement can be used after its definition in the subquery of another common table expression and in the main query as the data source data_source. It cannot be used in its own subquery or in the subqueries of preceding definitions. A common table expression is only known within the current WITH statement.
Each common table expression defined in a WITH statement must be used at least once within the WITH statement, either in another common table expression or in the main query. This means that the main query must access at least one common table expression.
The name +cte of a common table expression is valid across the entire WITH statement. The character + is omitted from the name of the substructure only when a substructure is created as a data source for a common table expression in an inline declaration with @DATA(...) in the INTO clause.
The result set of a common table expression never has a client column. Even if the client column of a client-dependent data source is included explicitly in the subquery to its SELECT list, it does not behave as such in the result set. For this reason, a query of the WITH statement that uses a common table expression as a data source cannot specify the addition USING or the obsolete addition CLIENT SPECIFIED.
The following additions can be used to expose hierarchies and associations for use in the current WITH statement:
The associations can be used in path expressions of the subsequent queries of the current WITH statement.
Hints
Example
The result set of the common table expression +cities is a list of all cities that are flown from or to by an airline. The common table expression is used in the main query as the data source of the subquery of the WHERE condition.
DATA carrid TYPE spfli-carrid VALUE 'LH'.
cl_demo_input=>request( CHANGING field = carrid ).
WITH
+cities AS (
SELECT cityfrom AS city
FROM spfli
WHERE carrid = @carrid
UNION DISTINCT
SELECT cityto AS city
FROM spfli
WHERE carrid = @carrid )
SELECT *
FROM sgeocity
WHERE city IN ( SELECT city
FROM +cities )
INTO TABLE @FINAL(result).
cl_demo_output=>display( result ).
Executable Examples
... ( name1, name2, ... )
Effect
Defines the column names of a common table expression +cte. The specified names name1, name2, ... are assigned to the columns defined in the SELECT list of the subquery of the common table expression in the order given. The names work like the alias names defined in the SELECT list using AS and overwrite these names.
The opening parenthesis must be directly after the name +cte1, +cte2, and so on. At least one blank must be placed after the opening parenthesis and in front of the closing parenthesis. There can be blanks in the comma-separated list of names.
The names can have a maximum of 30 characters, and can contain letters, numbers, and underscores. They must start with either a letter or an underscore.
Hint
It is possible to specify a name list if all columns with * are selected in the SELECT list of the subquery. This can lead to syntax errors if the data source of the subquery is subsequently extended.
Example
The result sets of both common table expressions +connections and +sum_seats are merged in the subquery of the common table expression +result in a join expression. An explicit name list assigns names to the resulting columns. These names are used in the main query to sort the results. For each flight connection of the selected airline, the total number of occupied seats is output from the DDIC database table SFLIGHT.
DATA from_id TYPE spfli-carrid VALUE 'AA'.
cl_demo_input=>add_field( CHANGING field = from_id ).
DATA to_id TYPE spfli-carrid VALUE 'UA'.
cl_demo_input=>request( CHANGING field = to_id ).
from_id = to_upper( from_id ).
to_id = to_upper( to_id ).
WITH
+connections AS (
SELECT spfli~carrid, carrname, connid, cityfrom, cityto
FROM spfli
INNER JOIN scarr
ON scarr~carrid = spfli~carrid
WHERE spfli~carrid BETWEEN @from_id AND @to_id ),
+sum_seats AS (
SELECT carrid, connid, SUM( seatsocc ) AS sum_seats
FROM sflight
WHERE carrid BETWEEN @from_id AND @to_id
GROUP BY carrid, connid ),
+result( name, connection, departure, arrival, occupied ) AS (
SELECT carrname, c~connid, cityfrom, cityto, sum_seats
FROM +connections AS c
INNER JOIN +sum_seats AS s
ON c~carrid = s~carrid AND
c~connid = s~connid )
SELECT *
FROM +result
ORDER BY name, connection
INTO TABLE @FINAL(result).
cl_demo_output=>display( result ).