SAP SELECT HIERARCHY ANCS AGG

Get Example source ABAP code based on a different SAP table
  SAP Help

• HIERARCHY_ANCESTORS_AGGREGATE ABAP_OSQL_TABLE_FUNC
• SOURCE HIERARCHY_ANCESTORS_AGGREGATE
• WHERE HIERARCHY_ANCESTORS_AGGREGATE
• START WHERE HIERARCHY_ANCESTORS_AGGREGATE
• MEASURES HIERARCHY_ANCESTORS_AGGREGATE

SELECT, hierarchy_ancestors_aggregate

ABAP_SYNTAX
... HIERARCHY_ANCESTORS_AGGREGATE(
SOURCE hierarchy $[AS tabalias$]
$[START WHERE sql_cond$]
MEASURES agg_func1 AS alias1$[,
agg_func2 AS alias2$[,
...$]$]
$[WHERE sql_cond$] ...

ABAP Addition
1 ... START WHERE sql_cond
2 ... WHERE sql_cond

What does it do?
Specifies the hierarchy aggregate navigator HIERARCHY_ANCESTORS_AGGREGATE as the 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 the parentheses and must be used in positions in which the SQL hierarchy is addressed.
The hierarchy navigator HIERARCHY_ANCESTORS_AGGREGATE aggregates the values of all ancestor nodes located between hierarchy nodes determined using WHERE and start nodes determined using START WHERE, including the values of the hierarchy nodes and start nodes themselves. A tabular result set is created consisting of all hierarchy nodes that

meet the WHERE condition and

have an ancestor node, including the hierarchy node itself, that meets the START WHERE condition as a start node.

Here, a separate row is created for each possible path to every start node that occurs under the ancestor nodes. If no WHERE condition is specified, this is done for all the hierarchy nodes. If no START WHERE condition is specified, the start node is the ancestor node implicitly with the lowest hierarchy level.
For each node of the result, the aggregate functions specified after MEASURES are applied to the hierarchy nodes of that path that consists of the node and its ancestor nodes, including the start node. The aggregation is based on the start node, which is significant for the aggregate function STRING_AGG. 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 their hierarchy columns. The column selector ~ can be used to prefix the columns with the name of the SQL hierarchy. The following aggregate functions can be used:
MIN( col ),
MAX( col ),
SUM( col ),
PRODUCT( col ),
COUNT( $[DISTINCT$] col ),
COUNT( * ), COUNT(*),
STRING_AGG( col$[, sep$] )
The addition DISTINCT is only possible for COUNT. The aggregate function PRODUCT can be used in this hierarchy navigator only. The addition ORDER BY is not possible in the function STRING_AGG.
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.



Latest notes:

If the START WHERE condition selects multiple start nodes, a separate row is inserted in the result set for each start node for which a path exists to one of the hierarchy nodes selected by the WHERE condition. Child nodes with multiple parent nodes can also point to different paths to a start node and hence to multiple rows f or a hierarchy node.

Additional hierarchy columns such as START_RANK and START_ID as for the hierarchy node navigators are not yet available, which means that the different paths in the result set cannot be distinguished from the content of hierarchy columns. It is currently advisable, therefore, to use the START WHERE condition to select exactly one start node only.

The fact that the start node determined by START WHERE is respected in the aggregation may be unexpected behavior and should be respected when formulating the condition. This can be done, for example, by setting a condition for the hierarchy column that determines the parent node and is not set on the key of the node.
NON_V5_HINTS

If the hierarchy navigator HIERARCHY_ANCESTORS_AGGREGATE is used, the syntax check is performed in ABAP_STRICT_777 strict mode from ABAP_RELEASE ABAP_777 / .
ABAP_HINT_END

ABAP_EXAMPLE_ABEXA
See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE
ABAP_EXAMPLE_END

ABAP Addition

What does it do?
The START WHERE condition selects the start nodes for the paths to be aggregated for the hierarchy nodes selected by the WHERE condition. Columns of the SQL hierarchy, including the additional hierarchy columns, can be used in the condition sql_cond.
If a selected start node is an ancestor node of a node selected by WHERE, the latter is added to the result set.



Latest notes:

If multiple start nodes are ancestor nodes of a node selected by WHERE, the latter is also added to the result set multiple times.

If a selected start node is not an ancestor node of a node selected by WHERE, the node is ignored.
NON_V5_HINTS
ABAP_HINT_END

ABAP Addition

What does it do?
The WHERE condition selects those nodes of the SQL hierarchy specified after SOURCE that can be 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:
Meeting the WHERE condition is by itself not sufficient to add nodes to the result set. An ancestor node must also meet the START WHERE in this case.
NON_V5_HINTS
ABAP_HINT_END