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

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

Syntax


... HIERARCHY_DESCENDANTS_AGGREGATE( 
      SOURCE
hierarchy [AS tabalias] 

     [JOIN data_source [AS tabalias] ON
sql_cond] 
      MEASURES agg_func1 AS alias1[, 

               agg_func2 AS alias2[, 

               ...]] 

     [WHERE sql_cond] 

     [WITH SUBTOTAL] 
     [WITH BALANCE] 

     [WITH NOT MATCHED] 
     [WITH TOTAL] ) ...

Extras

1. ... JOIN data_source [AS tabalias]

2. ... WHERE sql_cond

3. ... WITH SUBTOTAL

4. ... WITH BALANCE

5. ... WITH NOT MATCHED
6. ... WITH TOTAL

Effect

Specifies the hierarchy aggregate navigator HIERARCHY_DESCENDANTS_AGGREGATE as a data source data_source in a ABAP SQL query. It accesses the hierarchy hierarchy specified after SOURCE and evaluates it. After the hierarchy, AS can be used to specify an alias name for it. This name is valid within parentheses and must be used in positions in which the hierarchy is addressed.

The hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE returns a tabular result consisting of the hierarchy nodes of the hierarchy hierarchy specified after SOURCE. These nodes meet the optional WHERE condition. If no WHERE condition is specified, these are all the hierarchy nodes. For each node of the result, the aggregate functions specified after MEASURES are applied to the row set consisting of the node and all its descendant nodes. The result of each aggregate function is saved in the current node as the content of a separate column.

A comma-separated list of aggregate functions agg_func1, agg_func2, ... must be specified after MEASURES. At least one aggregate function must be specified. Each aggregate function must be assigned an alias name alias1, alias2, ... The arguments of the aggregate functions can be individual columns col of the hierarchy hierarchy including its herarchy columns or a data source data_source specified after JOIN. The column selector ~ can (or must) be used to prefix the columns with the name of the hierarchy or data source. The following aggregate functions are possible:

If JOIN is not specified, the arguments col can be columns of the hierarchy and the following aggregate functions can be used:

MIN( col ),
MAX( col ),
SUM( col ),
COUNT( [DISTINCT] col ),
COUNT( * ), COUNT(*)

The addition DISTINCT is only possible for COUNT.

If JOIN is specified, the arguments col can be columns of the hierarchy and the data source after data_source and the following aggregate functions can be used:

MIN( col ),
MAX( col ),
SUM( col ),
COUNT( [DISTINCT] col )

The addition DISTINCT is only possible for COUNT and for columns of the hierarchy.

The aggregate functions work as specified in the general description, except that only columns col are allowed as arguments and that the result of the function COUNT is INT8 instead of INT4. For each aggregate function, a column with the alias name defined by AS is added to the tabular result of the hierarchy navigator. This column contains the result of the aggregate function in every row. These columns are not hierarchy columns. If or ...~ is specified in the SELECT list, they are read like a column of the hierarchy specified as a source and are part of a structure or internal table created using an inline declaration @DATA(...) in the INTO clause.

Alongside the hierarchy columns of the hierarchy specified after SOURCE, the results set contains a further hierarchy column HIERARCHY_AGGREGATE_TYPE with the type INT1. In the rows of the results set created by the optional WITH additions, this column contains an indicator for the addition in question. In other cases it contains the numeric value 0.

Example

Calls the hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE in the program DEMO_HIERARCHY_AGGREGATE, where the CDS hierarchy DEMO_CDS_PARENT_CHILD_AGG is specified as a source. The WHERE condition evaluates the hierarchy column HIERARCHY_LEVEL and only nodes with a maximum hierarchy level of 2 are added to the results set. The aggregate functions, however, respect all descendant nodes regardless of their hierarchy level. When executed, this program demonstrates how this hierarchy navigator works.

DATA(level) = 2. 

SELECT agg~* 
       FROM HIERARCHY_DESCENDANTS_AGGREGATE( 
              SOURCE demo_cds_parent_child_agg( p_id = 'A' ) 
              MEASURES MIN( num ) AS min, 
                       MAX( num ) AS max, 
                       SUM( num ) AS sum, 
                       COUNT( * ) AS cnt 
              WHERE hierarchy_level <= @level ) AS agg 
       INTO TABLE @DATA(cds_hierarchy_desc_aggregate).

Addition 1

... JOIN data_source [AS tabalias]

Effect

The optional addition JOIN can be used to join an additional data source data_source with the hierarchy specified after SOURCE. The same applies to data_source as to every data source of a query, except that path expressions sql_path are not possible. After the data source, AS can be used to specify an alias name for it. This name is valid within parentheses and must be used in positions in which the hierarchy is addressed.

The results set of the hierarchy is joined with the results set of the data source data_source in accordance with LEFT OUTER JOIN rules. Here, the rules for conditions sql_cond in expressions apply to the ON condition. Columns of the data source can be used as arguments of the aggregate functions after MEASURES for which their data type is suitable. They are not, however, additional columns that are part of the tabular result of the hierarchy navigator.

For every node of the original hierarchy that meets the WHERE condition, the aggregate functions specified after MEASURES are applied to every descendant node in the results set of the join (as specified by the parent-child relationship).

Notes

The addition JOIN does not modify the number of rows in the results set of the hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE. If, however, the results set of the join contains more descendant nodes for a node than in the original hierarchy, all these nodes are respected by the aggregate functions. Any starting nodes in a calculation that occur more than once are also all respected.

If there are access conditions in CDS access control for a data source specified by JOIN and these are not switched off using WITH PRIVILEGED ACCESS, these conditions are applied before the join is made.

Executable Example

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE

Addition 2

... WHERE sql_cond

Effect

The WHERE condition selects those nodes of the hierarchy specified after SOURCE that are added to the results set of the hierarchy navigator. Columns of the hierarchy, including the additional hierarchy columns, can be used in the condition sql_cond.

Notes

The WHERE condition does not restrict the descendant nodes of the selected nodes. It is used only to select those nodes for which the aggregate function is calculated.
The columns of a data source joined using JOIN cannot be used after WHERE.

Addition 3

... WITH SUBTOTAL

Addition 4

... WITH BALANCE

Addition 5

... WITH NOT MATCHED

Addition 6

... WITH TOTAL

Effect

Each of the optional WITH additions (which can be specified in any order) adds precisely one row to the tabular result of the hierarchy aggregate navigator HIERARCHY_DESCENDANTS_AGGREGATE. In the new row, all columns (including the hierarchy columns) contain the null value, with the exception of columns created using the addition MEASURES and the hierarchy column HIERARCHY_AGGREGATE_TYPE. The special hierarchy column HIERARCHY_AGGREGATE_TYPE uses the following values to indicate which WITH addition added the row:

WITH Addition	HIERARCHY_AGGREGATE_TYPE
`SUBTOTAL`	1
`BALANCE`	2
`NOT MATCHED`	3
`TOTAL`	4

In the rows not created by WITH, the hierarchy column HIERARCHY_AGGREGATE_TYPE contains the numeric value 0. The columns created by the addition MEASURES contain the results of their aggregate functions for the hierarchy nodes created as follows by the WITH addition:

WITH SUBTOTAL

The aggregate functions evaluate all hierarchy nodes that meet the WHERE condition of the hierarchy navigator.

WITH BALANCE

The aggregate functions evaluate all hierarchy nodes that do not meet the WHERE condition of the hierarchy navigator.

WITH NOT MATCHED

This addition can only be specified in combination with the JOIN addition. The aggregate functions evaluate all rows of the data source data_source specified after JOIN for which the ON condition of the join is not met.

WITH TOTAL

The aggregate functions evaluate all hierarchy nodes plus the rows of a data source data_source specified after JOIN that do not meet the ON condition.

Note

If one of the WITH additions is used, the syntax check is made in strict mode from Release 7.54.

Executable Example

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE with WITH

Continue

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE with WITH