ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - FROM →  SELECT - FROM data_source →  SELECT - FROM hierarchy_data →  SELECT - FROM hierarchy 

SELECT - FROM HIERARCHY

Other versions: 7.31 | 7.40 | 7.54

Syntax

... HIERARCHY( SOURCE hierarchy_source [WITH PRIVILEGED ACCESS] 

               CHILD TO PARENT ASSOCIATION _hierarchy_assoc 

              [PERIOD FROM field1 TO field2 VALID FROM from TO to] 

               START WHERE sql_cond 

              [SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING][, 
  
                 
            field2 [ASCENDING|DESCENDING], ...]] 

              [DEPTH depth] 

              [MULTIPLE PARENTS {NOT ALLOWED}{LEAVES ONLY}|ALLOWED] 

              [ORPHANS IGNORE|ERROR|ROOT] 

              [CYCLES ERROR|BREAKUP] 

              [GENERATE SPANTREE] ) ... 

Extras

1. ... SOURCE hierarchy_source [WITH PRIVILEGED ACCESS]

2. ... CHILD TO PARENT ASSOCIATION _hierarchy_assoc

3. ... PERIOD FROM field1 TO field2 VALID FROM from TO to

4. ... START WHERE sql_cond

5. ... SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING], ...

6. ... DEPTH depth

7. ... MULTIPLE PARENTS {NOT ALLOWED}|{LEAVES ONLY}|ALLOWED

8. ... ORPHANS IGNORE|ERROR|ROOT

9. ... CYCLES ERROR|BREAKUP

10. ... GENERATE SPANTREE

Effect

Specifies the hierarchy generator HIERARCHY as a hierarchy hierarchy in an ABAP SQL query. The hierarchy generator generates the hierarchy. The following needs to be specified:

• SOURCE must be followed by the source hierarchy_source of the hierarchy.

• CHILD TO PARENT ASSOCIATION must be followed by a hierarchy association _hierarchy_assoc published by the source hierarchy_source. The source data source and target data source of this association must be the source hierarchy_source. The ON condition of the hierarchy association defines the parent-child relationships between the hierarchy nodes.

• START WHERE must be followed by a start condition that defines root nodes for the root node set of the hierarchy. The hierarchy consists of the root nodes of the root node set and their descendant nodes.

All other additions are optional and define further properties of the hierarchy. The rows of the tabular results set of the hierarchy generator are the hierarchy nodes of the generated hierarchy and their columns comprise the elements of the source specified after SOURCE and the additional hierarchy columns.

The hierarchy navigator HIERARCHY publishes all associations of the source hierarchy_source specified after SOURCE implicitly and leaves its target data source unchanged.

Notes

• The order of the additions is fixed. They must be specified as shown here.

• Rows of the results set of the source specified after SOURCE can occur more than once in the results set of the hierarchy generator, if selected by appropriate parent-child relationships.

• The hierarchy generator HIERARCHY in ABAP SQL works in the same way as the statement DEFINE HIERARCHY in ABAP CDS. Accessing the hierarchy generator HIERARCHY as the data source of a query is the same as accessing a CDS hierarchy defined accordingly.

• An association published implicitly by the hierarchy generator HIERARCHY can be used in path expressions or in further hierarchy generators.

Example

Specifies the hierarchy generator HIERARCHY as the data source of a SELECT statement in the program DEMO_HIERARCHY_TREE. The source of the hierarchy generator is a CDS view that uses the global temporary table DEMO_SIMPLE_TREE as a data source and whose CDS association _tree defines the parent-child association. The hierarchy generator selects the row with the value of start_id in the column ID as the root node and, starting from this node, inserts all descendant nodes in the results set that meet the ON condition of the hierarchy association. The results set contains all columns of the view DEMO_CDS_SIMPLE_TREE_SOURCE, plus all potential hierarchy columns. When executed, this program demonstrates the result of the SELECT statement.

DATA(start_id) = 1. 

SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_source 
                      CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = @start_id 
                       SIBLINGS ORDER BY parent 
                       MULTIPLE PARENTS NOT ALLOWED ) 
       FIELDS id, 
              parent, 
              name, 
              hierarchy_rank, 
              hierarchy_tree_size, 
              hierarchy_parent_rank, 
              hierarchy_level, 
              hierarchy_is_cycle, 
              hierarchy_is_orphan, 
              node_id, 
              parent_id 
       INTO TABLE @DATA(asql_result). 

Addition 1

... SOURCE hierarchy_source [WITH PRIVILEGED ACCESS]

Effect

The addition SOURCE specifies hierarchy_source as the source of the hierarchy generator. This source must publish the hierarchy association _hierarchy_assoc specified after CHILD TO PARENT ASSOCIATION. The following are potential sources hierarchy_source:

• A CDS view cds_view

cds_view can be specified as a CDS view that publishes the hierarchy association _hierarchy_assoc in its SELECT list.

• A common table expression +cte

+cte can be specified as a preceding common table expression in a WITH statement that publishes the hierarchy association _hierarchy_assoc using the addition WITH ASSOCIATIONS. The self association prerequisite can be met as follows:

• The addition REDIRECTED is used to replace the target data source of an association of a data source of the common table expression with the common table expression itself.

• JOIN TO ONE|MANY is used to define a CTE association as a self association for the common table expression.

• A nested hierarchy hierarchy

The hierarchy generator HIERARCHY can use other hierarchies hierarchy as a source. The following applies here:

• A CDS hierarchy cds_hierarchy must publish the hierarchy association _hierarchy_assoc in its element list. To meet the self association prerequisite, the association must be an additional association between the source of the CDS hierarchy and the CDS hierarchy.

• The hierarchy generator HIERARCHY publishes all associations of its source implicitly.

• A CTE hierarchy must use WITH ASSOCIATIONS to publish the hierarchy association _hierarchy_assoc. Furthermore, the addition REDIRECTED must be used to replace the target data source of the association in such a way that the self association prerequisite is met.

The hierarchy generator uses the result of a source specified after SOURCE as a source (its additional hierarchy columns are ignored). An outer hierarchy generator always adds its own hierarchy columns directly to the columns of the hierarchy specified after SOURCE. Any hierarchy columns listed explicitly in the element list of a CDS hierarchy that is specified as a source, on the other hand, are part of this hierarchy's results set and are respected accordingly.

If a CDS role is assigned to a CDS view specified as a source, the source is implicitly subject to CDS access control and only nodes that meet the access conditions of the CDS role are transferred to the hierarchy. When specifying the optional addition WITH PRIVILEGED ACCESS, the access control is deactivated and the access conditions are not evaluated.

Note

Hierarchies should only be nested in exceptional cases.

Example

Uses a CDS view and two common table expressions as the source of the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_GENERATOR.

• The first common table expression accesses the same CDS view and uses a path expression to publish the same hierarchy association. This expression must use REDIRECTED TO to redirect the hierarchy association to itself and hence meet the self association requirement.

• The second common table expression accesses the database table DEMO_SIMPLE_TREE in the same way as the CDS view and defines a separate CTE association as a self association that is then used as a hierarchy association.

All main queries have the same result.

DATA(start_id) = 1. 

"CDS view as source of hierarchy generator 
SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_source 
                       CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = @start_id 
                       SIBLINGS ORDER BY parent 
                       MULTIPLE PARENTS NOT ALLOWED ) 
       FIELDS name 
       INTO TABLE @DATA(cds_result). 

"CTE with association published as path as source of hierarchy generator 
WITH +tree AS ( 
        SELECT FROM demo_cds_simple_tree_source AS tree 
               FIELDS * ) 
        WITH ASSOCIATIONS ( \_tree 
                            REDIRECTED TO +tree 
                            VIA tree ) 
  SELECT FROM HIERARCHY( 
                SOURCE +tree 
                CHILD TO PARENT ASSOCIATION _tree 
                START WHERE id = @start_id ) 
         FIELDS name 
         INTO TABLE @DATA(cte_path_result). 
ASSERT cte_path_result = cds_result. 

"CTE with association defined as join as source of hierarchy generator 
WITH +simple_tree_source AS ( 
        SELECT FROM demo_simple_tree 
               FIELDS id, 
                      parent_id as parent, 
                      name ) 
        WITH ASSOCIATIONS ( JOIN TO ONE +simple_tree_source as _tree 
                             ON +simple_tree_source~parent = _tree~id ) 
  SELECT FROM HIERARCHY( 
                SOURCE +simple_tree_source 
                CHILD TO PARENT ASSOCIATION _tree 
                START WHERE id = @start_id ) 
         FIELDS name 
         INTO TABLE @DATA(cte_join_result). 
ASSERT cte_join_result = cds_result.

Example

Nests hierarchies in the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_NESTED. When executed, this program demonstrates the result of the SELECT statements. Three tree-like hierarchies that start at the row with the value 1 in the column ID are restricted to a depth of 2. From these hierarchies, further hierarchies are selected that start at the row with the value 5 in the column ID. The three inner hierarchies are specified as a CDS hierarchy, the hierarchy generator HIERARCHY, and as a CTE hierarchy. In the case of the CDS hierarchy and the CTE hierarchy, the hierarchy association must be verified as a self association explicitly. The three inner hierarchies have the same results sets, which means that the results of the three SELECT statements are also the same.

"CDS hierarchy as source of hierarchy generator 
SELECT FROM HIERARCHY( 
             SOURCE demo_cds_simple_tree_redir( p_id = 1, 
                                                p_depth = 2 ) 
              CHILD TO PARENT ASSOCIATION _redir 
              START WHERE id = 5 ) 
       FIELDS id, 
              parent, 
              hierarchy_rank, 
              hierarchy_level 
       INTO TABLE @DATA(hierarchy_cds_hierarchy). 

"Hierarchy generator as source of hierarchy generator 
SELECT FROM HIERARCHY( 
              SOURCE HIERARCHY( 
                       SOURCE demo_cds_simple_tree_source 
                       CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       DEPTH 2 ) 
              CHILD TO PARENT ASSOCIATION _tree 
              START WHERE id = 5  ) 
       FIELDS id, 
              parent, 
              hierarchy_rank, 
              hierarchy_level 
       INTO TABLE @DATA(hierarchy_hierarchy). 
ASSERT hierarchy_hierarchy = hierarchy_cds_hierarchy. 

"CTE hierarchy as source of hierarchy generator 
WITH +hierarchy AS ( 
        SELECT FROM HIERARCHY( 
                      SOURCE demo_cds_simple_tree_source 
                      CHILD TO PARENT ASSOCIATION _tree 
                      START WHERE id = 1 
                      DEPTH 2 ) AS hierarchy 
               FIELDS id, 
                      parent ) 
        WITH HIERARCHY hierarchy 
        WITH ASSOCIATIONS ( \_tree 
                            REDIRECTED TO +hierarchy 
                            VIA hierarchy ) 
  SELECT FROM HIERARCHY( 
                SOURCE +hierarchy 
                CHILD TO PARENT ASSOCIATION _tree 
                START WHERE id = 5 
                ) 
         FIELDS id, 
                parent_id, 
                hierarchy_rank, 
                hierarchy_level 
         INTO TABLE @DATA(hierarchy_cte_hierarchy). 
ASSERT hierarchy_cte_hierarchy = hierarchy_cds_hierarchy. 

Addition 2

... CHILD TO PARENT ASSOCIATION _hierarchy_assoc

Effect

The addition CHILD TO PARENT ASSOCIATION specifies the hierarchy association whose ON condition of the hierarchy generator selects the descendant nodes of the root node set. The hierarchy association must be published by the source hierarchy_source specified after SOURCE.

The hierarchy association defines the parent-child relationship between the hierarchy nodes. The following conditions apply here:

• The association must be a self-association.

• Only equality comparisons with the operator = and joined using AND can occur in the ON condition of the association.

• In each comparison in the ON condition, one field of the source data source must be compared with a field (prefixed with _hierarchy_assoc) of the target data source.

• The source data source of the association cannot contain any fields that have the same name as a hierarchy column. An alternative element name must be defined for these fields.

Each row of the results set of the source hierarchy_source that meets the ON condition for an existing hierarchy node is included recursively in the hierarchy as its child node (if this is possible).

Note

The optional additions define further conditions specifying whether a row can be included as a hierarchy node or not.

Example

The following CDS view publishes its CDS association _tree. This CDS association meets all requirements of a hierarchy association and can be used as one.

@AbapCatalog.sqlViewName: 'DEMOTREESOURCE'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view DEMO_CDS_SIMPLE_TREE_SOURCE
  as select from
    demo_simple_tree
    association [1..1] to DEMO_CDS_SIMPLE_TREE_SOURCE as _tree on
      $projection.parent = _tree.id
    {
      _tree,
      id,
      parent_id as parent,
      name
    }

Addition 3

... PERIOD FROM field1 TO field2 VALID FROM from TO to

Effect

Defines a hierarchy as a temporal hierarchy in which the hierarchy nodes are limited by an adjustment of time intervals.

• With field1 and field2, the fields of the source hierarchy_source are specified, which define the lower and upper limits of a period in the hierarchy details. field1 and field2 must be different fields of the same data type. This can be:

• The built-in type DATS of ABAP Dictionary.

• A data type that is defined by one of the data elements TIMESTAMP or TIMESTAMPL.

• from and to define the lower and upper limit of a time interval that acts as a condition for the periods of the root node set. For from and to, host variables and host expressions can be specified, whose ABAP data type matches the dictionary data type of field1 and field2 exactly. If field1 and field2 are date fields of the type DATS, text field literals of at least length 8 can also be specified for from and to.

A temporal hierarchy is generated as follows:

• Only root nodes of the root node set in which the period defined using field1 and field2 has a non empty intersection with the time interval defined by from and to are taken into account. This intersection forms the validity interval of the root node.

• Only child nodes in which the period defined by field1 and field2 has a non empty intersection with the validity interval of the parent node are generated. This intersection forms the validity interval of the child node.

For temporal hierarchies, there are additional hierarchy columns VALID_FROM and VALID_UNTIL, which contain the interval limits of the validity interval of each hierarchy node.

The addition PERIOD must not be used with GENERATE SPANTREE.

Notes

• The validity interval of a descendant node is always a subset of a validity interval of all ancestor nodes. Validity intervals can only remain the same or become narrower from hierarchy level to hierarchy level, they can never get wider.

• For a descendant node to belong to a temporal hierarchy, it is not sufficient for its period to overlap with the time interval defined by from and to. Only the validity interval of the parent node is decisive. A path of a normal hierarchy is truncated in a temporal hierarchy at the position in which there is no intersection between the period and the preceding validity interval.

• The source data source of the current hierarchy association must not have any fields called VALID_FROM or VALID_UNTIL. An alternative element name must be defined for these fields.

• The value of to can also be less than the value of from. However, a validity interval is formed where necessary. In contrast, if the value of the lower interval limit of the period is greater than the value of the upper interval limit, the validity interval is empty.

• Additions such as MULTIPLE PARENTS or CYCLES affect the temporal hierarchy. Nodes that would raise an exception in a normal hierarchy can be hidden in a temporal hierarchy.

• To generate a temporal hierarchy, in an SAP HANA database, the hierarchy generator function HIERARCHY_TEMPORAL there is called.

Example

Generation of two temporal hierarchies in the program DEMO_HIERARCHY_TEMPORAL, where in one date fields and in the other time stamp fields are used as periods.

SELECT FROM HIERARCHY( 
  SOURCE demo_cds_parent_child_src_prd 
  CHILD TO PARENT ASSOCIATION _relat 
  PERIOD FROM date_from TO date_to 
   VALID FROM @( CONV d( sy-datlo - 1 ) ) 
           TO @( CONV d( sy-datlo + 1 ) ) 
  START WHERE id = 'A' 
  SIBLINGS ORDER BY id ) 
       FIELDS id, 
              parent, 
              valid_from, 
              valid_until 
       INTO TABLE @DATA(asql_date_period) ##TYPE. 

GET TIME STAMP FIELD DATA(ts). 
SELECT FROM HIERARCHY( 
  SOURCE demo_cds_parent_child_src_prd 
  CHILD TO PARENT ASSOCIATION _relat 
  PERIOD FROM ts_from TO ts_to 
   VALID FROM @( CONV timestamp( 
                  cl_abap_tstmp=>subtractsecs( 
                     tstmp = ts 
                     secs  = 86400 ) ) ) 
           TO @( CONV timestamp( 
                  cl_abap_tstmp=>add( 
                     tstmp = ts 
                     secs  = 86400 ) ) ) 
  START WHERE id = 'A' 
  SIBLINGS ORDER BY id ) 
       FIELDS id, 
              parent, 
              valid_from, 
              valid_until 
       INTO TABLE @DATA(asql_ts_period) ##TYPE.

When executed, this program demonstrates how the PERIOD addition works. The date and time stamp fields of the database table DEMO_PARCHLD_PRD, which are accessed in the source of the hierarchy generator DEMO_CDS_PARENT_CHILD_SRC_PRD, are filled with data for this purpose. A normal hierarchy without the addition PERIOD and the two temporal hierarchies are shown. The validity intervals are also output for these.

• The temporal hierarchy based on date fields contains only the nodes with the values A, B, and C in the column ID. The period of the node with the value D does not overlap with the validity interval of the parent node with the value B. This means that the child node with the value E is truncated, irrespective of its period. All other nodes F to I haven empty periods and do not come into question anyway.

• The temporal hierarchy based on time stamps contains all nodes with periods TS_FROM to TS_TO that are not empty. The example illustrates how the validity intervals with the hierarchy levels can become narrower. In the last row, the validity interval corresponds to the previous, even though the period is wider.

Addition 4

... START WHERE sql_cond.

Effect

The addition START WHERE specifies the start condition for the hierarchy generator. START WHERE must be followed by a logical expression sql_cond that selects rows from the source hierarchy_source. The selected rows are inserted in the hierarchy as a root node set. For each root node in the root node set, the descendant nodes are selected that meet the ON condition of the hierarchy association and, if possible, inserted in the hierarchy.

Note

The start condition should select a feasible set of root nodes. If no rows in the results set of the source hierarchy_source meet the condition, the hierarchy is empty. If all rows meet the condition, the descendant nodes of every row are selected and inserted.

Example

Specifies an interval condition for the start condition of the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_START_WHERE. All rows in the CDS view specified as the source that meet the condition are inserted in the hierarchy as root nodes and all their descendant nodes are selected. When executed, this program permits different limits to be entered and displays the result.

DATA(from) = 'A '. 
DATA(to) =   'B '. 

SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                       CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id BETWEEN @from AND @to 
                       SIBLINGS ORDER BY parent 
                       MULTIPLE PARENTS ALLOWED 
                       CYCLES BREAKUP ) 
       FIELDS id, 
              parent 
       INTO TABLE @DATA(asql_result). 

Addition 5

... SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING], ...

Effect

The addition SIBLINGS ORDER BY sorts sibling nodes in the hierarchy generated by the hierarchy generator. If this addition is not used, the order of the sibling nodes is undefined.

Fields field1, field2, ... of the source cds_view can be specified in a comma-separated list after the addition hierarchy_source to specify the order of the sibling nodes.

The addition ASCENDING or DESCENDING can be specified for each field to specify an ascending or descending order (ascending is the default).

The fields specified after ORDER BY cannot be of the type LCHR, LRAW, STRING, RAWSTRING, or GEOM_EWKB.

Notes

• If specified, an additional general ORDER BY clause of the current query is applied to the hierarchy sorted by SIBLINGS ORDER BY.

• The additional hierarchy columns cannot be specified after SIBLINGS ORDER BY. They can, however, be specified in the general ORDER BY clause of the current query.

Example

Ascending and descending sort of sibling nodes in the program DEMO_HIERARCHY_SIBLINGS_ORDER. When executed, this program demonstrates how these additions work.

SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_source 
                      CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       SIBLINGS ORDER BY id ASCENDING ) 
       FIELDS id, 
              parent, 
              hierarchy_parent_rank 
       INTO TABLE @DATA(result_asc). 

SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_source 
                       CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       SIBLINGS ORDER BY id DESCENDING ) 
       FIELDS id, 
              parent, 
              hierarchy_parent_rank 
       INTO TABLE @DATA(result_desc). 

Addition 6

... DEPTH depth

Effect

The addition depth can be used to limit the number of hierarchy levels used to create descendant nodes. depth expects a host variable, a host expression, or a literal of type i. Only the types b, s, or i can be specified for depth.

The value in depth has the following meaning:

• For depth values greater then 0, the number of hierarchy edges are traversed that is specified in depth, starting from a root node.

• If the value of depth is 0, only the root nodes are inserted in the hierarchy.

• If depth is less than 0, no hierarchy nodes are created and sy-subrc is set to 4.

The addition DEPTH can be used only if the addition ORPHANS is not specified or is specified as ORPHANS IGNORE.

Example

Limits the number of hierarchy levels in the program DEMO_HIERARCHY_DEPTH. When executed, this program demonstrates how this addition works. It also shows the hierarchy level from the hierarchy column HIERARCHY_LEVEL.

SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_source 
                      CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       DEPTH 1 ) 
       FIELDS name, 
              hierarchy_level 
       INTO TABLE @DATA(result_1). 

Addition 7

... MULTIPLE PARENTS {NOT ALLOWED}|{LEAVES ONLY}|ALLOWED

Effect

The addition MULTIPLE PARENTS can be used to define whether the hierarchy generator is able to generate child nodes with multiple parent nodes:

• NOT ALLOWED

This is the default setting (a child node can have precisely on parent node only).

• LEAVES ONLY

Only leaf nodes can have multiple parent nodes.

• ALLOWED

All hierarchy nodes can have multiple parent nodes.

Example

Creates a hierarchy in the program DEMO_HIERARCHY_MULTI_PARENTS. Only leaf nodes can have multiple parents here.

SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                      CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id = 'A' 
                       MULTIPLE PARENTS LEAVES ONLY ) 
       FIELDS id, 
              parent, 
              hierarchy_level 
       INTO TABLE @DATA(leaves_only).

When executed, this program demonstrates how the MULTIPLE PARENTS addition works.

• The first SELECT statement does not specify the addition and NOT ALLOWED is applied implicitly. The hierarchy node with the ID D has three parent nodes and hence an exception is raised.

• The next SELECT statement specifies the addition MULTIPLE PARENTS LEAVES ONLY. The hierarchy node with the ID D is a leaf node, which means the function can be accessed.

• A child node for the row with the ID D is inserted in front of the next SELECT statement in the database table DEMO_PARENT_CHLD. If used, the addition MULTIPLE PARENTS LEAVES ONLY now also raises an exception.

• The next SELECT statement specifies the addition MULTIPLE PARENTS ALLOWED and it is possible to access the table again.

Addition 8

... ORPHANS IGNORE|ERROR|ROOT

Effect

The addition ORPHANS defines the way orphan nodes are handled. The following categories of orphan nodes exist:

• Hierarchy nodes that could have parent nodes (as specified by the parent-child relationship) but the parent nodes are not in the hierarchy (known as true orphans).

• Hierarchy nodes that cannot be reached from the root node set using hierarchy edges.

• Hierarchy nodes that are part of a node cycle and cannot be reached from the root node set using hierarchy nodes (known as island orphans).

The additions work as follows:

• IGNORE

This is the default setting (any orphan nodes are not inserted in the hierarchy).

• ERROR

Any orphan nodes detected raise an exception.

• ROOT

Orphan nodes are inserted in the hierarchy as follows:

• True orphans are included in the root node set as root nodes and flagged as orphan nodes in the hierarchy column HIERARCHY_IS_ORPHAN.

• Descendant nodes of true orphans are handled like descendant nodes of parent nodes from the root node set, but are also flagged as orphan nodes in the hierarchy column HIERARCHY_IS_ORPHAN.

• For the hierarchy nodes of island orphans, a parent node in the root node set is generated for the child node where the cycle occurs. In the generated root node, all columns of the source hierarchy_source contain the null value. In the hierarchy columns, the additional root node is flagged as an orphan node and PARENT_ID also contains the null value.

Example

Handles orphan nodes in the program DEMO_HIERARCHY_ORPHANS.

SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                      CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id = 'A' 
                       MULTIPLE PARENTS ALLOWED 
                       ORPHANS ROOT 
                       CYCLES BREAKUP ) 
       FIELDS CASE WHEN id IS NULL THEN '__' 
                   ELSE id 
                   END AS id, 
              CASE WHEN parent IS NULL THEN '__' 
                   ELSE parent 
                   END AS parent, 
              hierarchy_is_orphan, 
              hierarchy_is_cycle, 
              hierarchy_rank, 
              hierarchy_parent_rank, 
              hierarchy_level 
       INTO TABLE @DATA(root).

When executed, this program demonstrates how the ORPHANS addition works.

• The first SELECT statement specifies the addition ORPHANS IGNORE. All orphan nodes are ignored and only the root node set consisting of a single root node is read and inserted in the hierarchy.

• The next SELECT statement specifies the addition ORPHANS ERROR. The hierarchy would contain orphan nodes and hence an exception is raised.

• The next SELECT statement specifies the addition ORPHANS ROOT and all orphan nodes are inserted in the hierarchy:

• The hierarchy node with the value B in the column ID is a true orphan node. It contains a blank in the column PARENT_ID but there is no root node with a blank in the column ID. The addition ROOT includes the hierarchy node itself in the root node set (the hierarchy column HIERARCHY_PARENT_RANK has the value 0) and the row is flagged as an orphan node in the hierarchy column HIERARCHY_IS_ORPHAN.

• The hierarchy node with the value C in the column ID contains the value B in the column PARENT_ID. The parent node is an orphan node included in the root node set, which means that this hierarchy node is also flagged as an orphan node.

• The hierarchy node with the value X in the column ID also contains the value X in the column PARENT_ID. The hierarchy node then starts a node cycle where this node is itself the parent node. It is not part of the root node set, however, which means it is an island orphan. A parent node is inserted in the root node set in which the columns of the CDS view DEMO_CDS_PARENT_CHILD_SOURCE, plus the hierarchy column PARENT_ID, contain null values.

• The hierarchy nodes with the values U, V, and W in the column ID also comprise an island orphan. None of the hierarchy nodes in this node cycle is a root node. Again, a parent node is inserted in the root node set in which the columns of the CDS view, plus the hierarchy column PARENT_ID, contain null values.

Addition 9

... CYCLES ERROR|BREAKUP

Effect

The addition CYCLES defines how node cycles are defined. The additions work as follows:

• ERROR

This is the default setting (an exception is raised when a node cycle is detected).

• BREAKUP

The traversal of descendant nodes is broken at the child node where the node cycle occurs and the hierarchy column HIERARCHY_IS_CYCLE is set to the value 1.

If the addition BREAKUP is specified, MULTIPLE PARENTS ALLOWED must also be specified.

Example

Handles node cycles in the program DEMO_HIERARCHY_CYCLES.

SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                      CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id = 'A' OR 
                                  id = 'X' OR 
                                  id = 'Y' OR 
                                  id = 'Z' 
                       MULTIPLE PARENTS ALLOWED 
                       CYCLES BREAKUP ) 
       FIELDS id, 
              parent, 
              hierarchy_is_cycle, 
              hierarchy_level 
       INTO TABLE @DATA(breakup).

When executed, this program demonstrates how the CYCLES addition works.

• The first SELECT statement specifies the addition CYCLES BREAKUP. Here, the root node set consists of four root nodes of the CDS view DEMO_CDS_PARENT_CHILD_SOURCE and the following node cycles are detected:

• The child node with the value A in the column ID is also its own parent node. The hierarchy column HIERARCHY_IS_CYCLE contains the value 1 and no more descendant nodes are created.

• Starting from the hierarchy node with the value X in the column ID, child nodes are created with the values Y and Z and (again) X. The cycle is detected at the final child node, HIERARCHY_IS_CYCLE contains the value 1, and no more descendant nodes are created. The same applies to the descendant nodes starting from the root nodes with the values Y and Z.

• The second SELECT statement specifies the addition CYCLES ERROR and raises an exception.

Addition 10

... GENERATE SPANTREE

Effect

If the addition GENERATE SPANTREE is specified, the hierarchy generator inserts only child nodes without multiple parent nodes, starting from every root node. If, due to its parent-child relationships, a child node were to have multiple parent nodes after its root node, precisely one of the potential paths from the root node to this child node is selected and the child node is created for this path only.

• If the paths have different lengths, the shortest is selected.

• If the paths all have the same length, the first path found is selected.

If the addition GENERATE SPANTREE is specified, the following additions must be specified at the same time:

• MULTIPLE PARENTS ALLOWED

• ORPHANS IGNORE or ORPHANS not specified

• CYCLES BREAKUP

Notes

• If the parent-child relationships for the current data do not produce tree-like hierarchies, the addition GENERATE SPANTREE is ignored.

• Even if one path (from many paths) to a child node is selected, this does not mean that all others are rejected. In this case, only the edges leading to the child node are missing.

• The addition GENERATE SPANTREE can be used to detect whether at least one path leads from a root node to a child node without the results set needing to contain all paths.

• The addition GENERATE SPANTREE is used in an SAP HANA database to access the hierarchy generator function HIERARCHY_SPANTREE there.

Example

Uses GENERATE SPANTREE in the program DEMO_HIERARCHY_SPANTREE.

SELECT FROM HIERARCHY( SOURCE demo_cds_parent_child_source 
                      CHILD TO PARENT ASSOCIATION _relat 
                       START WHERE id = 'A' OR 
                                  id = 'K' OR 
                                  id = 'X' OR 
                                  id = 'Y' 
                       MULTIPLE PARENTS ALLOWED 
                       CYCLES BREAKUP 
                       GENERATE SPANTREE ) 
       FIELDS id, 
              parent 
       INTO TABLE @DATA(spantree).

When executed, this program demonstrates how the GENERATE SPANTREE addition works.

• The first SELECT statement does not specify the addition GENERATE SPANTREE. The hierarchy contains all possible paths from root nodes to child nodes.

• The second SELECT statement specifies the addition GENERATE SPANTREE. Starting from each root node, the results set only contains one path to the potential child nodes:

• From the root node with the value A in ID, two paths lead to D. Only the shorter path directly from A to D is created. The connection between C and D in the longer path is not created.

• From the root node with the value A in ID, two paths lead to N. Only one of the two paths from L to N or M to N is created.

• The child node with the value Z in ID has two parent nodes X and Y. Since both are root nodes, both paths are created.