ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → WITH → WITH - ASSOCIATIONS

WITH - ASSOCIATIONS, JOIN

Other versions: ~~7.31~~ | ~~7.40~~ | 7.54

Syntax


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

Addition

... TO ONE|MANY

Effect

If specified, JOIN initiates the definition and publication 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 associates the current common table expression +cte as a source data source with the target data source target specified in the definition of the CTE association using an ON condition sql_cond. Any 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 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:

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 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 ON condition must be specified in the SELECT list 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 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 results set merged using UNION. The columns specified in the ON condition refer to the union results set.
More specifically, internal tables or previously defined common table expressions of the same WITH statement (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 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 requires strict mode from Release 7.54.

Addition

... TO ONE|MANY

Effect

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.

Note

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

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

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.

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