Ansicht
Dokumentation

ABAPWITH_ASSOCIATIONS - WITH ASSOCIATIONS

BAL_S_LOG - Application Log: Log header data   CPI1466 during Backup  
This documentation is copyright by SAP AG.

WITH, ASSOCIATIONS

... WITH ASSOCIATIONS ( path
                       $|join$[,
                        ...$] )
                     $|(assoc_syntax)

Effect

The addition WITH ASSOCIATIONS for the definition of a common table expression exposes associations of this table expression for use in the subsequent queries of a WITH statement. The following can be specified:

• Existing CDS associations or CTE associations of the common table expression using SQL path expressions path.

• Definitions of new CTE associations of the common table expression using join.

The parentheses must contain at least one association and multiple associations can be specified as a comma-separated list. Both existing and new associations can be specified together and in any order.

• In subsequent queries of the current WITH statement, the exposed associations can be used as root elements of the path expressions in question.

• In the specified columns of SELECT statements of the subsequent queries.

• As a data source of the FROM clause of the subsequent queries.

• Exposed self-associations can be specified in the hierarchy generator HIERARCHY as a hierarchy association.

• An exposed association can be exposed again by a subsequent common table expression of the current WITH statement using the addition WITH ASSOCIATIONS, as long as this addition uses the exposing table expression as a data source. Different attributes can be specified here.

Instead of specifying associations statically in parentheses, a parenthesized data object assoc_syntax can be specified. When the statement is executed, the data object must contain the syntax shown for the static specification. In this case, the common table expression can be used only in other dynamic tokens of the WITH statement. The data object assoc_syntax can be a character-like data object or a standard table with a character-like row type. The syntax in assoc_syntax, as in the static syntax, is not case-sensitive. When an internal table is specified, the syntax can be distributed across multiple rows. Invalid syntax raises a catchable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.

If an association is exposed more than once under different alias names, it is handled as a separate association when used in a path expression. A separate instance of a join expression is created for each association used and each expression uses the result set of the exposing common table expression as its left side.

The addition WITH ASSOCIATIONS cannot be used if the subquery of the common table expression contains the language element UNION, INTERSECT, or EXCEPT. Furthermore, it is not possible to use the addition USING or the obsolete addition CLIENT SPECIFIED in the current CTE, nor in CTEs where the addition REDIRECTED TO is applied, nor in queries that use the association in a path expression and that transform it to a join expression.

Notes

• When an association of the data source of a common table expression is exposed using WITH ASSOCIATION, this is the same as exposing an association of the data source of a CDS view entity in its SELECT list.

• It should be noted that the exposing common table expression itself is the association source of the exposed association and not the data source of the expression. The exposing common table expression replaces the original association source of the association. The left side of a join expression created for the exposed association is the result set of the subquery of the common table expression.

Example

The following example program DEMO_WITH_ASSOCIATIONS demonstrates how CDS associations are exposed by common table expressions. It works in the same way as the executable example for using path expressions in the FROM clause. The main query uses the alias name _spfli_scarr to access the association target SCARR of the final CDS association _scarr of the path expression \_spfli[ fltime > @fltime ]\_scarr exposed for the common table expression +cte. The SELECT list of the common table expression must specify the component \_spfli-carrid with the same filter condition fltime > @fltime as in the exposed association so that the associated join can be created.

rdisp/max_wprun_time - Maximum work process run time   Addresses (Business Address Services)  
This documentation is copyright by SAP AG.

Length: 7740 Date: 20240419 Time: 214047     sap01-206 ( 109 ms )