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

WITH - HIERARCHY

Other versions: 7.31 | 7.40 | 7.54

Syntax

... WITH HIERARCHY hierarchy 
                 | (hierarchy_syntax) 

Effect

The addition WITH HIERARCHY publishes a common table expression as a CTE hierarchy. Under its name, a common table expression like this can be used in the subsequent queries of the current WITH statement as a hierarchy hierarchy.

Only a single data source can be accessed in the subquery of the common table expression and this data source must be a hierarchy hierarchy. The hierarchy used as the data source of the subquery can be specified either statically or dynamically after HIERARCHY.

• hierarchy is used to specify either the name of the hierarchy or its alias name defined using AS. When the hierarchy generator HIERARCHY is used, it must have an alias name and this name must be specified.
• A parenthesized data object hierarchy_syntax can be specified that, when the statement is executed, must either contain the static syntax specifying the hierarchy or be initial. In this case, the common table expression can be used only in other dynamic tokens of the WITH statement. The data object hierarchy_syntax can be a character-like data object or a standard table with a character-like row type. The syntax in hierarchy_syntax, as in the static syntax, is not case-sensitive. Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR. If hierarchy_syntax is initial, the common table expression is not published as a table expression.
  

The results set of a common table expression published as a CTE hierarchy includes the hierarchy columns of the hierarchy specified in the subquery. The SELECT list of the subquery cannot contain any columns with the same name or alias name as a hierarchy column.

Note

If hierarchy_syntax is specified dynamically, it is possible to decide at runtime whether a common table expression is published as a CTE hierarchy.

Example

Accesses CTE hierarchies in two hierarchy navigators HIERARCHY_DESCENDANTS (in the program DEMO_HIERARCHY_CTE) joined in the main query of a WITH statement using a left outer join. The first CTE hierarchy represents the CDS hierarchy DEMO_CDS_PARENT_CHILD accessed in its subquery. The second CTE hierarchy represents the results set of the hierarchy generator HIERARCHY used in its subquery. The ON condition of the join compares two hierarchy columns of the CTE hierarchies. When executed, this program demonstrates the result.

WITH +hierarchy1 AS ( 
        SELECT FROM demo_cds_parent_child( p_id = 'A' ) 
               FIELDS id AS id1, 
                      parent AS parent1 ) 
        WITH HIERARCHY demo_cds_parent_child, 
     +hierarchy2 AS ( 
        SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                              CHILD TO PARENT ASSOCIATION _relat 
                              START WHERE id = 'U' ) AS hierarchy 
               FIELDS id AS id2, 
                      parent AS parent2 ) 
        WITH HIERARCHY hierarchy 
  SELECT FROM HIERARCHY_DESCENDANTS( 
                SOURCE +hierarchy1 
                START WHERE hierarchy_parent_rank = 0 
                DISTANCE FROM 1 ) AS h1 
           LEFT OUTER JOIN 
             HIERARCHY_DESCENDANTS( 
               SOURCE +hierarchy2 
               START WHERE hierarchy_parent_rank = 0 
               DISTANCE FROM 1 ) AS h2 
                 ON h1~hierarchy_rank = h2~hierarchy_rank 
         FIELDS h1~id1, 
                h2~id2, 
                h1~parent1, 
                h2~parent2 
         INTO TABLE @DATA(result).