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 with WITH

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

Other versions: 7.31 | 7.40 | 7.54

Source Code

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

    fill_table( ).

    SELECT FROM demo_cds_parent_child( p_id = 'A' ) AS h
                  RIGHT OUTER JOIN demo_child_num AS j
                    ON j~id = h~id
           FIELDS
              COALESCE( CAST( h~id AS CHAR( 4 ) ),
                        'Null' ) AS id,
              COALESCE( CAST( h~parent AS CHAR( 4 ) ),
                        'Null' ) AS parent,
              COALESCE( CAST( hierarchy_level AS CHAR( 20 ) ),
                        'Null' ) AS hierachy_level,
              j~num AS num
           INTO TABLE @DATA(joined_hierarchy).
    cl_demo_output=>write( joined_hierarchy ).

    SELECT 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
              WITH SUBTOTAL
              WITH BALANCE
              WITH NOT MATCHED
              WITH TOTAL ) AS agg
            FIELDS
              COALESCE( CAST( id AS CHAR( 4 ) ),
                        'Null' ) AS id,
              COALESCE( CAST( parent AS CHAR( 4 ) ),
                        'Null' ) AS parent,
              COALESCE( CAST( hierarchy_level AS CHAR( 20 ) ),
                        'Null' ) AS hierachy_level,
              COALESCE( CAST( hierarchy_aggregate_type AS CHAR( 4 ) ),
                        'Null' ) AS hierarchy_aggregate_type,
              min,
              max,
              sum,
              cnt
           INTO TABLE @DATA(asql_hierarchy_desc_aggregate).

    SELECT 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
              WITH SUBTOTAL
              WITH BALANCE
              WITH NOT MATCHED
              WITH TOTAL ) AS agg
            FIELDS
              COALESCE( CAST( id AS CHAR( 4 ) ),
                        'Null' ) AS id,
              COALESCE( CAST( parent AS CHAR( 4 ) ),
                        'Null' ) AS parent,
              COALESCE( CAST( hierarchy_level AS CHAR( 20 ) ),
                        'Null' ) AS hierachy_level,
              COALESCE( CAST( hierarchy_aggregate_type AS CHAR( 4 ) ),
                        'Null' ) AS hierarchy_aggregate_type,
              min,
              max,
              sum,
              cnt
           INTO TABLE @DATA(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( asql_hierarchy_desc_aggregate ).

Description

It demonstrates the way the different WITH additions work in the hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE. The source is a hierarchy joined to a further data source by the JOIN addition. Before the function is called, the results set of a join is produced containing all rows to which the WITH additions are applied. Depending on the value entered for the host variable level used in the WHERE condition, the additions WITH SUBTOTAL and WITH BALANCE return different results. The results of WITH NOT MATCHED and WITH TOTAL are independent of level.

The function COALESCE is used here to highlight the null values in the output. If this function is not used, the null values are converted into type-dependent initial values and hence cannot be detected as null values.