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_ANCESTORS_AGGREGATE 

Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE

This example demonstrates the hierarchy navigator HIERARCHY_ANCESTORS_AGGREGATE.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(start_id) = 'A '.
    DATA(end_id1)  = 'F1'.
    DATA(end_id2)  = 'D2'.
    cl_demo_input=>new(
      )->add_field( CHANGING field = start_id
      )->add_field( CHANGING field = end_id1
      )->request(   CHANGING field = end_id2 ).

    fill_table( ).

    SELECT *
           FROM HIERARCHY(
             SOURCE demo_cds_parent_child_src_agg
             CHILD TO PARENT ASSOCIATION _relat
             START WHERE id = 'A'
             SIBLINGS ORDER BY id )
           INTO TABLE @DATA(hierarchy).
    cl_demo_output=>write( hierarchy ).

    SELECT FROM HIERARCHY_ANCESTORS_AGGREGATE(
                  SOURCE HIERARCHY(
                    SOURCE demo_cds_parent_child_src_agg
                    CHILD TO PARENT ASSOCIATION _relat
                    START WHERE id = 'A'
                    SIBLINGS ORDER BY id )
                  START WHERE id <= @start_id
                  MEASURES COUNT(*) AS cnt,
                           MIN( num ) AS min,
                           MAX( num ) AS max,
                           SUM( num ) AS sum,
                           PRODUCT( num ) AS prod,
                           STRING_AGG( id, '-' ) AS strg_agg
                  WHERE id = @end_id1 OR
                        id = @end_id2 ) AS agg
           FIELDS id,
                  cnt, min, max, sum, prod, strg_agg
           INTO TABLE @DATA(asql_hiera_ancestors_aggregate).

    SELECT FROM HIERARCHY_ANCESTORS_AGGREGATE(
                  SOURCE demo_cds_parent_child_agg( p_id = 'A' )
                  START WHERE id <= @start_id
                  MEASURES COUNT(*) AS cnt,
                           MIN( num ) AS min,
                           MAX( num ) AS max,
                           SUM( num ) AS sum,
                           PRODUCT( num ) AS prod,
                           STRING_AGG( id, '-' ) AS strg_agg
                  WHERE id = @end_id1 OR
                        id = @end_id2 ) AS agg
           FIELDS id,
                  cnt, min, max, sum, prod, strg_agg
           INTO TABLE @DATA(cds_hiera_ancestors_aggregate).

    ASSERT
      cds_hiera_ancestors_aggregate = asql_hiera_ancestors_aggregate.

    DELETE FROM demo_parchld_agg. "GTT!

    cl_demo_output=>display( asql_hiera_ancestors_aggregate ).

Description

Calls of the hierarchy navigator HIERARCHY_ANCESTORS_AGGREGATE. If the example is executed with the proposed input values, the results set contains two rows, representing paths from a starting node with the ID A to two hierarchy nodes with the IDs F1 and D2. The paths are highlighted as a result of the aggregate function STRING_AGG.

If the input value for the starting node is raised, further starting nodes are added by the relational operator after START WHERE and hence further rows are added to the results set. In this example, the aggregated paths can be taken from the last column, however there is currently no way of interpreting the starting node from the hierarchy columns.