SAP WITH ABAP Statements

Get Example source ABAP code based on a different SAP table
  ABAP Statement

ID WITH
• SELECT WITH
• WITH ABAP_STATEMENT_OSQL
• AS ( SELECT ... ) WITH

WITH
Short Reference

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

ABAP Addition
... ( name1, name2, ... )

What does it do?
The AB-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:

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. In addition, 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 result set of the common table expression. The set operators UNION, INTERSECT , and EXCEPT can be used to combine the result sets of multiple subqueries. In this case, special rules query_clauses apply when 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 result set of the main query is assigned to ABAP data objects according to the INTO clause.

As described in SELECT, a single- or multirow result 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 be closed with the ENDWITH statement. ENDWITH has exactly the same meaning for WITH ... SELECT 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 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
BEGIN_SECTION VERSION 5 OUT or the obsolete addition CLIENT SPECIFIED
END_SECTION VERSION 5 OUT .
The following additions can be used to expose hierarchies and associations for use in the current WITH statement:

The addition hierarchy can be used to expose the common table expression as a CTE hierarchy.

The addition associations can be used to do the following:

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

Define and expose dedicated CTE associations. The associations can be used in path expressions of the subsequent queries of the current WITH statement.



Latest notes:

A common table expression creates a temporary tabular result set, which can be accessed during execution of the WITH statement. Common table expressions can therefore be considered temporary views, which only exist for the duration of the database access.

Common table expressions can be used whenever intermediate results are required in a SELECT statement. AB-SQL statements are more readable if common table expressions are used, and a common table expression is always useful if intermediate results are required more than once. If required, common table expressions can also perform the tasks of GTTs.

Using common table expressions as a data source also provides the option of selecting directly from a subquery SELECT FROM subquery, which is not possible in AB_SQL .

The character + in front of the name of a common table expression marks it as such, just like the character @ for host variables. Due to the character +, a common table expression cannot have the same name as a table from the ABAP Dictionary and hence cannot be hidden.

For WITH loops that are closed with ENDWITH, the same notes apply as to SELECT loops. In particular, WITH loops should not be nested.

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

The addition USING can be used in the queries of a WITH statement, as is usual for client-dependent data sources, to switch implicit client handling there.

If the WITH statement is used, the syntax check is performed in the ABAP_STRICT_765 strict mode for ABAP_RELEASE ABAP_765 / .
ABAP_HINT_END

ABAP_EXAMPLE_VX5
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.
ABEXA 00767
ABAP_EXAMPLE_END

ABAP_EXAMPLES_ABEXA

WITH, Common Table Expressions

WITH, Client Handling

WITH, Aggregation for Join Set
ABAP_EXAMPLE_END

ABAP Addition

What does it do?
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 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.



Latest notes:
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.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
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.
ABEXA 00768
ABAP_EXAMPLE_END

Return to menu