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_ANCESTORS_AGGREGATE
SOURCE hierarchy [AS tabalias]
[START WHERE sql_cond]
MEASURES agg_func1 AS alias1[,
agg_func2 AS alias2[,
[WHERE sql_cond] ...
Specifies the hierarchy
HIERARCHY_ANCESTORS_AGGREGATE as a data source
data_source in a ABAP SQL
query. It accesses the
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_ANCESTORS_AGGREGATE aggregates the values of all
ancestor nodes located between
hierarchy nodes determined using
starting nodes determined
START WHERE, including the values of the hierarchy nodes and starting nodes themselves. A tabular results set is created consisting of all hierarchy nodes that
have an ancestor node (including the hierarchy node itself) that meets the
START WHEREcondition as a starting node.
Here, a separate row is created for each possible path to every starting node that occurs under the
ancestor nodes. If no
WHEREcondition is specified, this is done for all the hierarchy nodes. If no
START WHEREcondition is specified, the starting node is, implicitly, the ancestor node with the lowest hierarchy level.
For each node of the result, the aggregate functions specified after
are applied to the hierarchy nodes of that path that consists of the node and its ancestor nodes (including
the starting node). The aggregation is based on the starting node, which is significant for the aggregate
STRING_AGG. 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
agg_func2, ... must be
MEASURES. At least one aggregate function must be specified.
Each aggregate function must be assigned an alias name
... The arguments of the aggregate functions can be individual columns
col of the hierarchy
hierarchy, including their
hierarchy columns. The
~ can be used to prefix the columns with the name of the hierarchy. The following aggregate functions can be used:
DISTINCT is only possible for
The aggregate function
PRODUCT can be used in this hierarchy navigator only.
ORDER BY is not possible in the function
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
...~ 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
- If the
START WHEREcondition selects multiple starting nodes, a separate row is inserted in the results set for each starting node for which a path exists to one of the hierarchy nodes selected by the
WHEREcondition. Child nodes with multiple parent nodes can also point to different paths to a starting node and hence to multiple rows for a hierarchy node.
- Additional hierarchy columns such as START_RANK and START_ID are not yet available in the
hierarchy node navigators,
which means that the different paths in the results set cannot be distinguished from the content of
hierarchy columns. It is currently advisable, therefore, to use the
START WHEREcondition to select precisely one starting node only.
- The fact that the starting node determined by
START WHEREis respected in the aggregation can be unwanted behavior and should be covered by 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.
- If the hierarchy navigator
HIERARCHY_ANCESTORS_AGGREGATEis used, the syntax check is made in strict mode from Release 7.54.
... START WHERE sql_cond
START WHERE condition selects the starting nodes for the paths in question
for the hierarchy nodes selected by the
WHERE condition. Columns of the hierarchy, including the additional
hierarchy columns, can be used in the condition
If a selected starting node is an ancestor node of a node selected by
WHERE, the latter is added to the results set.
If multiple starting nodes are ancestor nodes of a node selected by
WHERE, the latter is also added to the results set multiple times.
If a selected starting node is not an ancestor node of a node selected by
WHERE, the node is ignored.
... WHERE sql_cond
WHERE condition selects those nodes of the hierarchy specified after
SOURCE that can be added to the results set of the hierarchy navigator. Columns of the hierarchy, including the additional
hierarchy columns, can be used in the condition
WHERE condition is by itself not enough to add nodes to the results set. An ancestor node must also meet the
START WHERE in this case.