SAP SELECT HIERARCHY DESC AGG Get Example source ABAP code based on a different SAP tableSAP Help
• HIERARCHY_DESCENDANTS_AGGREGATE ABAP_OSQL_TABLE_FUNC
• SOURCE HIERARCHY_DESCENDANTS_AGGREGATE
• WHERE HIERARCHY_DESCENDANTS_AGGREGATE
• ON HIERARCHY_DESCENDANTS_AGGREGATE
• JOIN HIERARCHY_DESCENDANTS_AGGREGATE
• MEASURES HIERARCHY_DESCENDANTS_AGGREGATE
• WITH SUBTOTAL HIERARCHY_DESCENDANTS_AGGREGATE
• WITH BALANCE HIERARCHY_DESCENDANTS_AGGREGATE
• WITH NOT MATCHED HIERARCHY_DESCENDANTS_AGGREGATE
• WITH TOTAL HIERARCHY_DESCENDANTS_AGGREGATE
SELECT, FROM hierarchy_descendants_aggregate ABAP_SYNTAX ... HIERARCHY_DESCENDANTS_AGGREGATE( hierarchy $[AS tabalias$] data_source $[AS tabalias$] ON sql_cond$] sql_cond$] ABAP Addition ... JOIN data_source $[AS tabalias$] ... WHERE sql_cond ... WITH SUBTOTAL ... WITH BALANCE ... WITH NOT MATCHED ... WITH TOTAL What does it do? hierarchy aggregate navigator HIERARCHY_DESCENDANTS_AGGREGATE as a data source data_source in an AB_SQL 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. 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. 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. 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. 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. ABAP_EXAMPLE_VX5 HIERARCHY_DESCENDANTS_AGGREGATE in the class CL_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, CL_DEMO_HIERARCHY_AGGREGATE demonstrates how this hierarchy navigator works. ABAP Addition What does it do? 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. 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 are not part of the tabular result of the hierarchy navigator as additional columns. 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. Latest 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. 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. ABAP_EXAMPLE_ABEXA Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE ABAP Addition What does it do? 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. Latest 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. ABAP Addition ABAP Addition ABAP Addition ABAP Addition What does it do? 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<)> AdditionHIERARCHY_AGGREGATE_TYPE SUBTOTAL1 BALANCE2 NOT MATCHED3 TOTAL4 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. Latest notes: WITH additions is used, the syntax check is performed in ABAP_STRICT_777 strict mode from ABAP_RELEASE ABAP_777 / . ABAP_EXAMPLE_ABEXA Hierarchy Navigator HIERARCHY_DESCENDANTS_AGGREGATE with WITH
