WITH - ASSOCIATIONS, JOIN
... JOIN TO ONE|MANY target AS _assoc
ON sql_cond ...
A CTE association associates the current common table expression
a source data source with the target data source
target specified in the
definition of the CTE association using an
data sources visible and usable in this position can be specified for
AS must be used to specify a name
assoc for the
CTE association under which it can be addressed in the subsequent queries of the current
WITH statement. The name can contain letters, digits, the minus sign
-), and the underscore (
) in any order.
By specifying its name, a CTE association of a common table expression can be used in the subsequent queries of the same
WITH statement, in all operand positions for associations. These are elements of
path expressions or any hierarchy associations specified.
When a CTE association is used in a path expression, it is transformed to a join expression. The source
data source represents the left side and the target data source represents the right side. The
ON condition of the CTE association is added to the
ON condition of the join. The category of the join is determined by where the path expression is used:
FROM, it is an inner join (
- As a specified column, it is a left outer join (
LEFT OUTER JOIN)
The following applies when the
sql_cond is specified:
- Any fields in the target data source can be prefixed with the name of the CTE association
_assoc.. Any fields in the source data source can be prefixed with the name of the common table expression
+cte. The prefix is separated using the column selector
~. These items are mandatory only if the names of the fields occur in both data sources.
- Any fields of the source data source specified in the
ONcondition must be specified in the
SELECTlist of the common table expression. This ensures that a join expression can be built from the CTE association (when used in a path expression). The names defined in the common table expression must be used here. These are either the alias names defined using
ASor the names defined in an optional name list.
- A CTE association cannot be used in the same common table expression for which it is defined.
A common table expression for which a CTE association is defined can also be a results set merged using
UNION. The columns specified in the
ONcondition refer to the union results set.
More specifically, internal tables or previously defined
common table expressions of the same
WITHstatement (plus the current common table expression itself) can be specified as target data sources
target. If specified in this case, the current common table expression is a self association.
A common table expression that defines and publishes a self association can be used as the source of the
HIERARCHY. More specifically, this makes it possible to also use internal tables as the source of hierarchies (see the executable example).
It is advisable to use an underscore
_as the first character of the CTE association name.
+cannot be used as the first character of a CTE association name, which means there can be no naming conflicts in the
The definition of a CTE association requires strict mode from Release 7.54.
... TO ONE|MANY
The mandatory items
TO ONE or
TO MANY define the
cardinality of the target data source of the CTE association. This cardinality is used by some database
systems for optimizations. In these database systems, any
LEFT OUTER JOINs
produced by a path expression are given the addition TO ONE if TO
ONE is specified and the addition TO MANY if any other cardinality
is used. These additions work in the same way as when they are specified explicitly in
LEFT OUTER JOIN. This means that an optimization is attempted and the result can be undefined if the results set does not match the cardinality.
To avoid undefined and platform-dependent behavior, the cardinality should always be defined to match the data in question.
WITH statement from the program DEMO_WITH_ASSOCIATIONS_JOIN demonstrates the way
CTE associations are defined and used. The example works in exactly the same way as the
executable example for path expressions
FROM clause. The CDS views and CDS associations used here are replaced
one on one by common table expressions and CTE associations. When executed, the program displays the result and compares the behavior of CTE associations and CDS associations when accessed.
DATA: tz TYPE s_tzone VALUE 'UTC+1', currc TYPE s_currcode VALUE 'EUR', fltime TYPE s_fltime VALUE 0. WITH +spfli_scarr AS ( SELECT FROM spfli FIELDS carrid, airpfrom, fltime ) WITH ASSOCIATIONS ( JOIN TO ONE scarr AS _scarr ON +spfli_scarr~carrid = _scarr~carrid ), +sairport_tz AS ( SELECT FROM sairport FIELDS id WHERE time_zone = @tz ) WITH ASSOCIATIONS ( JOIN TO ONE +spfli_scarr AS _spfli ON +sairport_tz~id = _spfli~airpfrom ) SELECT DISTINCT carrname FROM +sairport_tz \_spfli[ fltime > @fltime ] \_scarr[ currcode = @( CONV s_currcode( to_upper( currc ) ) ) ] AS scarr ORDER BY carrname INTO TABLE @DATA(result_cte_assoc).
WITH statement from the program DEMO_WITH_ASSOCIATIONS_HIERA
demonstrates the way a common table expression
+parent_child_source and its CTE association
_relat are used as the data source and
hierarchy association of the
HIERARCHY. The CTE association
_relat is a
When executed, the program displays the result and compares it with the result when a similar CDS view and CDS association are used in the hierarchy generator.
DATA(start_id) = CONV demo_parent_chld-id( 'A' ). WITH +parent_child_source AS ( SELECT FROM demo_parent_chld FIELDS id, parent_id AS parent ) WITH ASSOCIATIONS ( JOIN TO MANY +parent_child_source AS _relat ON +parent_child_source~parent = _relat~id ) SELECT FROM HIERARCHY( SOURCE +parent_child_source CHILD TO PARENT ASSOCIATION _relat START WHERE id = @start_id SIBLINGS ORDER BY id MULTIPLE PARENTS ALLOWED ) FIELDS id, parent, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(cte_result).