Ansicht
Dokumentation

ABENSELECT_HIERARCHY_DESC_AGG - SELECT HIERARCHY DESC AGG

ABENSELECT_HIERARCHY_DESC_AGG - SELECT HIERARCHY DESC AGG

PERFORM Short Reference   ROGBILLS - Synchronize billing plans  
This documentation is copyright by SAP AG.
SAP E-Book

SELECT, FROM hierarchy_descendants_aggregate

... 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$] ) ...


Additions

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 an query. It accesses the SQL hierarchy hierarchy specified after SOURCE and evaluates it. After the SQL hierarchy, AS can be used to specify an alias name for it that is valid within parentheses and must be used in positions in which the SQL hierarchy is addressed.

The hierarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE returns a tabular result consisting of the hierarchy nodes of the SQL 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 resulting from the node and all its descendant nodes. The result of each aggregate function is stored 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 SQL hierarchy hierarchy including its hierarchy 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 SQL hierarchy or data source. The following aggregate functions are possible:

  • If JOIN is not specified, the arguments col can be columns of the SQL 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 SQL 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 SQL hierarchy.

The aggregate functions work as described 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 SQL hierarchy specified as a source and are part of a structure or internal table created using an inline declaration @DATA$|@FINAL(...) in the INTO clause.

In addition to the hierarchy columns of the SQL hierarchy specified after SOURCE, the result set contains a further hierarchy column HIERARCHY_AGGREGATE_TYPE with the type INT1. In the rows of the result set created by the optional WITH additions, this column contains an indicator for the addition to be created, otherwise it contains the numeric value 0.

Example

Call of 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 result set. The aggregate functions, however, respect all descendant nodes regardless of their hierarchy level. When executed, this program demonstrates how this hierarchy navigator works.

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 SQL 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 that is valid within parentheses and must be used in positions in which the data source is addressed.

The result set of the SQL hierarchy is joined with the result set of the data source data_source in accordance with LEFT OUTER JOIN rules. The rules for conditions sql_cond in expressions apply to the ON condition. Columns of the data source can be used as arguments of those aggregate functions after MEASURES for which their data type is suitable. However, they do not contribute as additional columns to the tabular result of the hierarchy navigator.

For every node of the original SQL hierarchy that meets the WHERE condition, the aggregate functions specified after MEASURES are applied to every descendant node in the result set of the join according to the parent-child relationship.

Notes

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

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE

Addition 2

... WHERE sql_cond

Effect

The WHERE condition selects those nodes of the SQL hierarchy specified after SOURCE that are added to the result set of the hierarchy navigator. Columns of the SQL 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 exactly 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, except 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 together 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 performed in strict mode from Release .

Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE with WITH






General Material Data   TXBHW - Original Tax Base Amount in Local Currency  
This documentation is copyright by SAP AG.

Length: 16226 Date: 20240329 Time: 012807     sap01-206 ( 318 ms )