WITH - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Reads →

WITH

Quick Reference

Syntax

WITH
  +cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] )[,
  +cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] ),
  ... ]
  SELECT mainquery_clauses
         [UNION ...]
         INTO|APPENDING target
         [additional_options].
  ...
[ENDWITH].

Addition:

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

Effect

The Open 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 results 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 result sets of multiple subqueries. In this case, additional 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. For WITH ... SELECT, ENDWITH has exactly the same meaning as ENDSELECT 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 result 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. That is, 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 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, in a query of the WITH statement that uses a common table expression as a data source, the addition CLIENT SPECIFIED cannot be specified.

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. Open 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 Open 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 CLIENT SPECIFIED can be used in the queries of a WITH statement, as is usual for client-specific data sources, to deactivate automatic 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 the SAP 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

Definition of 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 alternative column 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
ENDWITH
WITH Examples