ABAPWITH_ASSOCIATIONS_DEFINING - WITH ASSOCIATIONS DEFINING

TXBHW - Original Tax Base Amount in Local Currency   General Material Data  
This documentation is copyright by SAP AG.

WITH, ASSOCIATIONS, JOIN

... JOIN TO ONE$|MANY target AS _assoc
      ON sql_cond ...

Addition:

... TO ONE$|MANY

Effect

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.

Notes

• 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 result set merged using UNION, INTERSECT, or EXCEPT. The columns specified in the ON condition refer to the merged result set.

• 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.

• 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 (see the executable example).

• 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.

• The definition of a CTE association enforces strict mode from Release .
  
  

Addition

... TO ONE$|MANY

Effect

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.

Note

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

Example

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.

Example

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.

CL_GUI_FRONTEND_SERVICES - Frontend Services   BAL Application Log Documentation  
This documentation is copyright by SAP AG.

Length: 10120 Date: 20230206 Time: 101223     sap01-206 ( 149 ms )