WITH - ASSOCIATIONS
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
Definitions of new CTE
associations of the common table expression using
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
SELECTstatements of the subsequent queries.
Published self associations can be specified in the
HIERARCHYas a hierarchy association.
A published association can be published again by a subsequent common table expression of the current
WITHstatement 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
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.
WITH ASSOCIATIONS cannot be used if the subquery of the common
table expression contains the language element
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.
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.
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
of the path expression
_spfli[ fltime > @fltime ]_scarr published for the
common table expression +cte. The
of the common table expression must specify the component
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 ).