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

WITH

Quick Reference

Other versions: ~~7.31~~ | ~~7.40~~ | 7.54

Syntax


WITH 
  +cte1[( name1, name2, ... )] AS ( SELECT
subquery_clauses [UNION ...] ), 

    [hierarchy] 

    [associations][, 

  +cte2[( name1, name2, ... )] AS ( SELECT
subquery_clauses [UNION ...] ), 

    [hierarchy] 

    [associations], 

  ... ] 
  SELECT mainquery_clauses 

         [UNION ...] 

         INTO|APPENDING target 

         [UP TO ...] [OFFSET ...] 

         [abap_options]. 

  ... 
[ENDWITH].

Addition

... ( name1, name2, ... )

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 results 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:

A comma-separated list with at least one definition of a common table expression

Each common table expression has a unique name +cte1, +cte2, ... . The names cte can have a maximum of 30 characters, and can contain letters, numbers, and underscores. They must start with either a letter or an underscore. Also, the name must be prefixed with the character +. The initial + character is part of the name, but cannot stand alone and must not be followed by a number.

An optional name list (( name1, name2, ... )) for the columns of the result set of the common table expression can be specified directly after the name (see below).

A parenthesized subquery SELECT subquery_clauses follows the AS. This subquery creates the tabular results set of the common table expression. The language element UNION can be used to combine the results sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

A closing main query SELECT mainquery_clauses, which can contain the same clauses and additions (apart from FOR ALL ENTRIES) as a standalone SELECT statement, and works in the same way:

The results set of the main query is assigned to ABAP data objects according to the INTO clause.

As described in SELECT, a single- or multirow results set is created and, depending on the target area specified in the INTO clause, a SELECT loop is either opened or not.

A SELECT loop must close with the ENDWITH statement. ENDWITH has exactly the same meaning for WITH ... SELECT that ENDSELECT has for a standalone SELECT loop.

In the main query, each of the previously defined common table expressions +cte1, +cte2, and so on, can be used as the data source data_source.

The language element UNION can be used to combine the results sets of multiple main queries. In this case, additional rules query_clauses apply for 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 full 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 results set of a common table expression never has a client column. Even if the client column of a client-specific data source is added explicitly in the subquery to its SELECT list, it does not behave as such in the results 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 publish hierarchies and associations for use in the current WITH statement:

The addition hierarchy can be used to publish the common table expression as a CTE hierarchy.
The addition associations can be used to do the following:

Publish the CDS associations of a common table expression when CDS views are accessed.

Define and publish dedicated CTE associations.

The associations can be used in path expressions of the subsequent queries of the current WITH statement.

Notes

A common table expression creates a temporary tabular results set, which can be accessed during execution of the WITH statement. Common table expressions can therefore be thought of as temporary views, which only exist for the duration of the database access.
Common table expressions can be used whenever subtotals are required in a SELECT statement. ABAP SQL statements are more readable if common table expressions are used, and a common table expression is always useful if subtotals is required more than once. If required, common table expressions can also perform the tasks of GTTs.
Using common table expressions also provides the option of selecting directly from a subquery SELECT FROM subquery, which is not possible in ABAP SQL.
The character + used as a prefix labels a table expression as a common table expression, just like the character @ for host variables. The character + prevents a common table expression from having the same name as a table from the ABAP Dictionary and hence obscuring it.
For WITH loops that are closed with ENDWITH, the same notes apply as for SELECT loops. In particular, WITH loops should not be nested.
The addition USING can be used in the queries of a WITH statement, as is usual for client-specific data sources, to switch implicit client handling there.
In the main query of the WITH statement, if * is specified in the SELECT list, this means that there is no unconverted transfer to a work area specified after INTO.
The WITH statement bypasses table buffering.
If the WITH statement is used, syntax buffering is executed in the strict mode for Release7.51.

Example

The results 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 @DATA(result). 

cl_demo_output=>display( result ).

Executable Examples

Addition

... ( 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.

If a name list is specified, it must contain a name for each column of the common table expression.
If no name list is specified, the columns of the results list have the names defined in the SELECT list of the subquery.

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 before 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.

Note

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 then extended.

Example

The results 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 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 @DATA(result). 

cl_demo_output=>display( result ).

Continue

WITH subquery_clauses

WITH - mainquery_clauses