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

WITH - ASSOCIATIONS

Other versions: 7.31 | 7.40 | 7.54

Syntax

... WITH ASSOCIATIONS ( path 

                 
     |join[, 

                        ...] ) 

                     |(assoc_syntax) 

Effect

The addition WITH ASSOCIATIONS used to define a common table expression publishes 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 published 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.

• Published self associations can be specified in the hierarchy generator HIERARCHY as a hierarchy association.
• A published association can be published again by a subsequent common table expression of the current WITH statement using the addition WITH ASSOCIATIONS, as long as this addition uses the publisher 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 displayed for the statically specified information. 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 span multiple rows. Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.

If an association is published more than once under different alias names, it as 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 results set of the publisher 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. As before, it is not possible to use the addition USING or 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 published using WITH ASSOCIATION, this is the same as publishing an association of the data source of a CDS view in its SELECT list.
• It should be noted that the publisher common table expression itself is the source data source of the published association and not the data source of the expression. The publisher common table expression replaces the original source data source of the association. The left side of an instance of a join expression created for the published association is the results set of the subquery of the common table expression.
  

Example

The following example program DEMO_WITH_ASSOCIATIONS demonstrates how CDS associations are published 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 target data source SCARR of the final CDS association _scarr of the path expression _spfli[ fltime > @fltime ]_scarr published 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 published association. This ensures that an instance of the associated join can be created.

DATA: 
  tz     TYPE s_tzone    VALUE 'UTC+1', 
  currc  TYPE s_currcode VALUE 'EUR', 
  fltime TYPE s_fltime   VALUE 0. 

cl_demo_input=>new( 
  )->add_field( CHANGING field = tz 
  )->add_field( CHANGING field = currc 
  )->add_field( CHANGING field = fltime )->request( ). 

WITH 
  +cte AS ( SELECT \_spfli[ fltime > @fltime ]-carrid 
                   FROM demo_cds_assoc_sairport_tz( 
                     tz = @( to_upper( tz ) ) ) ) 
       WITH ASSOCIATIONS ( \_spfli[ fltime > @fltime ] 
                          \_scarr AS _spfli_scarr ) 
  SELECT DISTINCT carrname 
         FROM +cte\_spfli_scarr[ 
                currcode = @( CONV s_currcode( to_upper( currc ) ) ) ] 
                  AS scarr 
         ORDER BY carrname 
         INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

Continue

WITH - ASSOCIATIONS, path

WITH - ASSOCIATIONS, JOIN