ABAP - Keyword Documentation →  ABAP - Programming Language →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Read Access → 
Mail Feedback

WITH

Short Reference

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


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

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



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.

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



Continue
WITH, subquery_clauses
WITH, mainquery_clauses
WITH, HIERARCHY
WITH, ASSOCIATIONS
ENDWITH
WITH, Examples