Skip to content

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

Other versions: 7.31 | 7.40 | 7.54

Syntax


... HIERARCHY_ANCESTORS_AGGREGATE( 
      SOURCE
hierarchy [AS tabalias] 

     [START WHERE sql_cond] 

      MEASURES agg_func1 AS alias1[, 
   
           agg_func2 AS alias2[, 

               ...]] 

     [WHERE sql_cond] ...

Extras

1. ... START WHERE sql_cond

2. ... WHERE sql_cond

Effect

Specifies the hierarchy aggregate navigator HIERARCHY_ANCESTORS_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_ANCESTORS_AGGREGATE aggregates the values of all ancestor nodes located between hierarchy nodes determined using WHERE and starting nodes determined using 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

  • meet the WHERE condition and
  • have an ancestor node (including the hierarchy node itself) that meets the START WHERE condition 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 WHERE condition is specified, this is done for all the hierarchy nodes. If no START WHERE condition 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 MEASURES 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 function 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 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 their hierarchy columns. The column selector ~ can be used to prefix the columns with the name of the 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 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.


Notes

  • If the START WHERE condition 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 WHERE condition. 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 WHERE condition to select precisely one starting node only.

  • The fact that the starting node determined by START WHERE is 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.

Executable Example

See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE

Addition 1

... START WHERE sql_cond

Effect

The 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 sql_cond.

If a selected starting node is an ancestor node of a node selected by WHERE, the latter is added to the results set.


Notes

  • 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.

Addition 2

... WHERE sql_cond

Effect

The 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 sql_cond.


Note

Meeting the 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.

Continue

Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE