We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!
... JOIN TO ONE$|MANY target AS _assoc
ON sql_cond ...
A CTE association joins the current common table expression +cte as an association source with the association target target specified in the definition of the CTE association using an ON condition sql_cond. All data sources visible and usable in this position can be specified for target.
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 the specification of hierarchy associations.
When a CTE association is used in a path expression, it is transformed to a join expression. The association source represents the left side and the association target 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:
The following applies when the ON condition sql_cond is specified:
... TO ONE$|MANY
The mandatory specifications TO ONE or TO MANY define the cardinality of the association target of the CTE association. This cardinality is used by some database systems for optimizations. In these database systems, any left outer joins (LEFT OUTER JOIN) produced by a variant of 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 result set does not match the cardinality.
To avoid undefined and platform-dependent behavior, the cardinality should always be defined to match the data to be read.
The following 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 in the FROM clause. The CDS views and CDS associations used here are replaced one by 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.
The following 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 generator HIERARCHY. The CTE association _relat is a self-association. 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.