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_navigator

SELECT - FROM hierarchy_node_navigator

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

Syntax


... {HIERARCHY_DESCENDANTS 
    |HIERARCHY_ANCESTORS 

    |HIERARCHY_SIBLINGS}( 
       SOURCE hierarchy 

       START WHERE sql_cond 

      [DISTANCE [FROM n1] [TO n2]] ) ...

Variants

1. HIERARCHY_DESCENDANTS( ... )

2. HIERARCHY_ANCESTORS( ... )

3. HIERARCHY_SIBLINGS( ... )

Addition

... DISTANCE [FROM n1] [TO n2]

Effect

Specifies a hierarchy node navigator as a data source data_source in a ABAP SQL query. It accesses the hierarchy hierarchy specified after SOURCE and evaluates it.

START WHERE must be followed by a logical expression sql_cond containing hierarchy nodes of the hierarchy after SOURCE. The columns of the hierarchy can also be used as operands here. Any hierarchy nodes that meet the conditions are the starting nodes of the hierarchy navigators. They evaluate the hierarchy hierarchy specified after SOURCE, starting from all starting nodes, and insert the selected hierarchy nodes in its results set.

Alongside the hierarchy columns of the hierarchy specified after SOURCE, the results set of each of these hierarchy navigators contains two further hierarchy columns:

START_RANK with the type INT8

START_ID with the type SSTRING and length 1333

In each row of the results set, these columns contain the values of the hierarchy columns HIERARCHY_RANK and NODE_ID of the associated starting nodes.

Variant 1

HIERARCHY_DESCENDANTS( ... )

Effect

The hierarchy navigator HIERARCHY_DESCENDANTS selects all descendant nodes of the starting nodes, plus the starting nodes themselves, from the hierarchy hierarchy specified after SOURCE. Alongside the hierarchy columns of the hierarchy specified after SOURCE, plus START_RANK and START_ID, the results set contains a further hierarchy column, HIERARCHY_DISTANCE, with the type INT4, which contains the distance to the starting node in question. This distance is the positive difference between the hierarchy levels HIERARCHY_LEVEL of the individual child nodes and the starting nodes in question.

Example

Calls the hierarchy navigator HIERARCHY_DESCENDANTS in the program DEMO_HIERARCHY_NAVIGATORS, where the hierarchy generator HIERARCHY is specified as a source. When executed, this program demonstrates how this hierarchy navigator works. It also displays the value of the hierarchy column HIERARCHY_DISTANCE.

DATA(id) = 1. 

SELECT FROM HIERARCHY_DESCENDANTS( 
              SOURCE HIERARCHY( 
                       SOURCE demo_cds_simple_tree_source 
                       CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       SIBLINGS ORDER BY id ASCENDING ) 
             START WHERE id = @id ) 
       FIELDS id, 
              parent_id, 
              hierarchy_rank, 
              hierarchy_level, 
              hierarchy_tree_size, 
              hierarchy_distance 
         INTO TABLE @DATA(descendants).

Variant 2

HIERARCHY_ANCESTORS( ... )

Effect

The hierarchy navigator HIERARCHY_ANCESTORS selects all ancestor nodes of the starting nodes, plus the starting nodes themselves, from the hierarchy specified after SOURCE. Alongside the hierarchy columns of the hierarchy specified after SOURCE, plus START_RANK and START_ID, the results set contains a further hierarchy column, HIERARCHY_DISTANCE, with the type INT4, which contains the distance to the starting node in question. This distance is the negative difference between the hierarchy levels HIERARCHY_LEVEL of the individual parent nodes and the starting nodes in question.

Example

Calls the hierarchy navigator HIERARCHY_ANCESTORS in the program DEMO_HIERARCHY_NAVIGATORS, where the CDS hierarchy DEMO_CDS_SIMPLE_TREE is specified as a source. When executed, this program demonstrates how this hierarchy navigator works. It also displays the value of the additional hierarchy column HIERARCHY_DISTANCE.

DATA(id) = 1. 

SELECT FROM HIERARCHY_ANCESTORS( 
              SOURCE demo_cds_simple_tree( p_id = 1 ) 
              START WHERE id = @id ) 
       FIELDS id, 
              parent_id, 
              hierarchy_rank, 
              hierarchy_level, 
              hierarchy_tree_size, 
              hierarchy_distance 
       INTO TABLE @DATA(ancestors).

Variant 3

HIERARCHY_SIBLINGS( ... )

Effect

The hierarchy navigator HIERARCHY_SIBLINGS selects all sibling nodes of the starting nodes, plus the starting nodes themselves, from the hierarchy specified after SOURCE. Alongside the hierarchy columns of the hierarchy specified after SOURCE, plus START_RANK and START_ID, the results set contains a further hierarchy column, HIERARCHY_SIBLING_DISTANCE, with the type INT4, which contains the distance to the starting node in question. This is the positive or negative difference between the numbers HIERARCHY_RANK of the sibling nodes and the starting nodes in question.

Note

If the values in the additional column HIERARCHY_SIBLING_DISTANCE are accessed, the addition SIBLINGS ORDER BY should be used in the hierarchy specified after SOURCE. If not, the values remain undefined.

Example

Calls the hierarchy navigator HIERARCHY_SIBLINGS in the program DEMO_HIERARCHY_NAVIGATORS, where the CTE hierarchy +hierarchy is specified a source. When executed, this program demonstrates how this hierarchy navigator works. It also displays the value of the additional hierarchy column HIERARCHY_SIBLING_DISTANCE.

DATA(id) = 1. 

WITH +hierarchy AS ( 
        SELECT FROM HIERARCHY( 
                      SOURCE demo_cds_simple_tree_source 
                      CHILD TO PARENT ASSOCIATION _tree 
                      START WHERE id = 1 
                      SIBLINGS ORDER BY id ASCENDING ) AS hierarchy 
               FIELDS id, 
                      parent ) WITH HIERARCHY hierarchy 
  SELECT FROM HIERARCHY_SIBLINGS( 
                SOURCE +hierarchy 
                START WHERE id = @id ) 
         FIELDS id, 
                parent_id, 
                hierarchy_rank, 
                hierarchy_level, 
                hierarchy_tree_size, 
                hierarchy_sibling_distance 
         INTO TABLE @DATA(siblings).

Addition

... DISTANCE [FROM n1] [TO n2]

Effect

In the case of the hierarchy navigators HIERARCHY_DESCENDANTS and HIERARCHY_ANCESTORS, the addition DISTANCE restricts the distance to the starting node in question. The addition DISTANCE cannot be specified for the hierarchy navigator HIERARCHY_SIBLINGS.

At least one of the additions FROM or TO must be specified in the order shown. n1 and n2 expect host variables, host expressions, or literals with the type i. Only the types b, s, or i can be used. Only those hierarchy nodes are selected for which the value of the hierarchy column HIERARCHY_DISTANCE is between the values of n1 and n2. If the value of n1 is greater than n2, no hierarchy node is selected. If TO, is not specified, there is no upper limit. If FROM, is not specified, there is no lower limit.

Notes

The way the addition DISTANCE works is matched by using the relational operator BETWEEN for the hierarchy column HIERARCHY_DISTANCE. In this case, the selection is applied to the results set of the hierarchy navigator rather than being performed by the navigator itself.
To produce hierarchy nodes in the results set of HIERARCHY_DESCENDANTS, the interval boundaries must include positive values. To produce hierarchy nodes in the results set of HIERARCHY_ANCESTORS, the interval boundaries must include negative values.
To exclude the starting nodes from the results set, the additions FROM and TO can be used as follows:

If FROM 1 is specified without the addition TO, HIERARCHY_DESCENDANTS selects only descendant nodes

If TO -1 is specified without the addition FROM, HIERARCHY_ANCESTORS selects only the ancestor nodes

Example

Calls the hierarchy navigator HIERARCHY_DESCENDANTS in the program DEMO_HIERARCHY_DISTANCE while specifying the addition DISTANCE. When executed, this program demonstrates how this addition works. If, for example, the value 2 is specified for n1 and 3 is specified for n2, only the grandchildren nodes and great-grandchildren nodes of the starting node are selected. If -3 is specified for n1 and -2 is specified for n2, the results set of HIERARCHY_DESCENDANTS is empty. For these values, on the other hand, the results set of HIERARCHY_ANCESTORS contains the grandparents and great-grandparents of the starting node.

DATA(id) = 1. 
DATA(n1) = 2. 
DATA(n2) = 3. 

SELECT FROM HIERARCHY_DESCENDANTS( 
             SOURCE HIERARCHY( 
                      SOURCE demo_cds_simple_tree_source 
                       CHILD TO PARENT ASSOCIATION _tree 
                       START WHERE id = 1 
                       SIBLINGS ORDER BY id ASCENDING ) 
            START WHERE id = @id 
            DISTANCE FROM @n1 TO @n2 ) 
       FIELDS id, 
              parent_id, 
              hierarchy_rank, 
              hierarchy_level, 
              hierarchy_tree_size, 
              hierarchy_distance 
       INTO TABLE @DATA(descendants).