SAP WITH ASSOCIATIONS DEFINING ABAP Statements



Get Example source ABAP code based on a different SAP table
  


• JOIN WITH ASSOCIATIONS
• ON WITH ASSOCIATIONS

WITH, ASSOCIATIONS, JOIN

ABAP_SYNTAX
... JOIN cardinality target AS _assoc
ON sql_cond ...

ABAP Addition
... cardinality

What does it do?
Specifying JOIN initiates the definition and exposure of a CTE association with the name _assoc in the addition WITH ASSOCIATIONS when a common table expression is defined in a WITH statement.
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:
After FROM, it is an inner join ( INNER JOIN)
As a specified column, it is a left outer join (LEFT OUTER JOIN)
The following applies when the ON condition sql_cond is specified:
Any fields in the association target can be prefixed with the name of the CTE association _assoc. . Any fields in the association source can be prefixed with the name of the common table expression +cte. The prefix is separated using the column selector ~. These specifications are mandatory only if the names of the fields occur in both data sources.
Any fields of the association source specified in the ON condition must be listed in the SELECT list of the common table expression so 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 AS or the names defined in an optional name list.



Latest notes:
A CTE association cannot be used in the same common table expression for which it is defined.
More specifically, internal tables or previously defined common table expressions of the same WITH statement and the current common table expression itself can also be specified as association targets. The latter is a self-association.
It is advisable to use an underscore _ as the first character of the CTE association name.
The character + cannot be used as the first character of a CTE association name, which means there can be no naming conflicts in the ON condition.
A common table expression that defines and exposes a self-association can be used as the source of the hierarchy generator HIERARCHY. More specifically, this makes it possible to also use internal tables as the source of hierarchies.
NON_V5_HINTS
For the latter, see the executable example.
A common table expression for which a CTE association is defined can also be a result set merged using UNION, INTERSECT, or EXCEPT. The columns specified in the ON condition refer to the merged result set.
The definition of a CTE association enforces ABAP_STRICT_774 strict mode from ABAP_RELEASE ABAP_774 / .
ABAP_HINT_END
• TO ONE WITH ASSOCIATIONS
• TO MANY WITH ASSOCIATIONS
• ONE TO ONE WITH ASSOCIATIONS
• ONE TO EXACT ONE WITH ASSOCIATIONS
• ONE TO MANY WITH ASSOCIATIONS
• EXACT ONE TO EXACT ONE WITH ASSOCIATIONS
• EXACT ONE TO ONE WITH ASSOCIATIONS
• EXACT ONE TO MANY WITH ASSOCIATIONS
• MANY TO EXACT ONE WITH ASSOCIATIONS
• MANY TO ONE WITH ASSOCIATIONS
• MANY TO MANY WITH ASSOCIATIONS

ABAP Addition

What does it do?
It is mandatory to specify a cardinality cardinality. A cardinality for both the source and the target, or only for the association target of the CTE association is possible. The following options are available:
ONE TO ONE
ONE TO MANY
ONE TO EXACT ONE
EXACT ONE TO ONE
EXACT ONE TO MANY
EXACT ONE TO EXACT ONE
MANY TO ONE
MANY TO MANY
MANY TO EXACT ONE
TO ONE
TO MANY
This cardinality is used by some database systems for performance optimizations. This works in the same way as for cardinality specifications in join expressions. This means that an optimization is attempted, and the result can be undefined if the result set does not match the cardinality.



Latest notes:
To avoid undefined and platform-dependent behavior, the cardinality should always be defined to match the data to be read.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
The following WITH statement from the class CL_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 executing CL_DEMO_WITH_ASSOCIATIONS_JOIN, the class displays the result and compares the behavior of CTE associations and CDS associations when accessed.
ABEXA 00770
ABAP_EXAMPLE_END

ABAP_EXAMPLE_VX5
The following WITH statement from the class CL_DEMO_WITH_ASSCTNS_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 executing CL_DEMO_WITH_ASSCTNS_HIERA, the class displays the result and compares it with the result when a similar CDS view and CDS association are used in the hierarchy generator.
ABEXA 00771
ABAP_EXAMPLE_END

Return to menu