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_aggregate_navigator →  SELECT - FROM HIERARCHY_DESCENDANTS_AGGREGATE 

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE

This example demonstrates the hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE with the addition JOIN.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(level) = 2.
    cl_demo_input=>request( CHANGING field = level ).

    DATA itab TYPE SORTED TABLE OF demo_child_num
              WITH NON-UNIQUE KEY client id.

    fill_table( ).

    SELECT FROM demo_cds_parent_child( p_id = 'A' )
           FIELDS id,
                  parent,
                  hierARCHY_level
           INTO TABLE @DATA(hierarchy).
    cl_demo_output=>write( hierarchy ).

    cl_demo_output=>line( ).

    SELECT FROM demo_cds_parent_child( p_id = 'A' ) AS h
             LEFT OUTER JOIN demo_child_num AS j
               ON j~id = h~id
           FIELDS h~id,
                  h~parent,
                  j~num,
                  h~hierarchy_level
           INTO TABLE @DATA(hierarchy_joined).
    cl_demo_output=>write( hierarchy_joined ).

    SELECT agg~*
           FROM HIERARCHY_DESCENDANTS_AGGREGATE(
                  SOURCE HIERARCHY(
                           SOURCE demo_cds_parent_child_source
                           CHILD TO PARENT ASSOCIATION _relat
                           START WHERE id = 'A'
                           SIBLINGS ORDER BY id ) AS h
                  JOIN demo_child_num AS j
                    ON j~id = h~id
                  MEASURES MIN( j~num ) AS min,
                           MAX( j~num ) AS max,
                           SUM( j~num ) AS sum,
                           COUNT( j~num ) AS cnt
                  WHERE hierarchy_level <= @level ) AS agg
           INTO TABLE @DATA(asql_hierarchy_desc_aggregate).
    cl_demo_output=>write( asql_hierarchy_desc_aggregate ).

    SELECT agg~*
           FROM HIERARCHY_DESCENDANTS_AGGREGATE(
                  SOURCE demo_cds_parent_child( p_id = 'A' ) AS h
                  JOIN demo_child_num AS j
                    ON j~id = h~id
                  MEASURES MIN( j~num ) AS min,
                           MAX( j~num ) AS max,
                           SUM( j~num ) AS sum,
                           COUNT( j~num ) AS cnt
                  WHERE hierarchy_level <= @level ) AS agg
           INTO TABLE @DATA(cds_hierarchy_desc_aggregate).
    ASSERT
      cds_hierarchy_desc_aggregate = asql_hierarchy_desc_aggregate.

    cl_demo_output=>line( ).

    SELECT *
           FROM demo_child_num
           INTO TABLE @itab.
    itab = VALUE #( BASE itab
      ( client = sy-mandt id = 'A ' num = -1     )
      ( client = sy-mandt id = 'BC' num = 10000  )
      ( client = sy-mandt id = 'BD' num = 100000 )
      ( client = sy-mandt id = 'BD' num = 200000 )
      ( client = sy-mandt id = 'BD' num = 300000 ) ).

    SELECT FROM demo_cds_parent_child( p_id = 'A' ) AS h
             LEFT OUTER JOIN @itab AS j
               ON j~id = h~id
           FIELDS h~id,
                  h~parent,
                  j~num,
                  h~hierarchy_level
           INTO TABLE @hierarchy_joined.
    cl_demo_output=>write( hierarchy_joined ).

    SELECT agg~*
           FROM HIERARCHY_DESCENDANTS_AGGREGATE(
                  SOURCE HIERARCHY(
                           SOURCE demo_cds_parent_child_source
                           CHILD TO PARENT ASSOCIATION _relat
                           START WHERE id = 'A'
                           SIBLINGS ORDER BY id ) AS h
                  JOIN @itab AS j
                    ON j~id = h~id
                  MEASURES MIN( j~num ) AS min,
                           MAX( j~num ) AS max,
                           SUM( j~num ) AS sum,
                           COUNT( j~num ) AS cnt
                  WHERE hierarchy_level <= @level ) AS agg
           INTO TABLE @asql_hierarchy_desc_aggregate.
    cl_demo_output=>write( asql_hierarchy_desc_aggregate ).

    SELECT agg~*
           FROM HIERARCHY_DESCENDANTS_AGGREGATE(
                  SOURCE demo_cds_parent_child( p_id = 'A' ) AS h
                  JOIN @itab AS j
                    ON j~id = h~id
                  MEASURES MIN( j~num ) AS min,
                           MAX( j~num ) AS max,
                           SUM( j~num ) AS sum,
                           COUNT( j~num ) AS cnt
                  WHERE hierarchy_level <= @level ) AS agg
           INTO TABLE @cds_hierarchy_desc_aggregate.
    ASSERT
      cds_hierarchy_desc_aggregate = asql_hierarchy_desc_aggregate.

    DELETE FROM demo_parent_chld. "GTT!
    DELETE FROM demo_child_num.   "GTT!

    cl_demo_output=>display( ).

Description

Calls the hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE where the CDS hierarchy DEMO_CDS_PARENT_CHILD specified as a source is joined with a database table DEMO_CHILD_NUM and an internal table @itab using the addition JOIN. The aggregate functions evaluate the column NUM of the joined data sources. The results of the join expressions used are also displayed.

• In the first part, the table DEMO_CHILD_NUM is filled to produce a 1:1 relationship between the rows of the hierarchy and its own rows. The result of the hierarchy navigator is the same as in the executable example DEMO_HIERARCHY_AGGREGATE, but here the number values are taken from the joined table.

• In the second part, the internal table @itab contains the rows of the database table DEMO_CHILD_NUM and further rows that also match the criteria for descendant nodes for nodes of the hierarchy. This example shows how these additional rows, produced in the results set of the join, are evaluated in the aggregate functions.

The statement ASSERT shows that the use of the hierarchy generator HIERARCHY and a semantically identical CDS hierarchy as the source of the hierarchy navigator produces the same results.