Ansicht
Dokumentation

ABENSELECT_HIERARCHY_ANCS_AGG - SELECT HIERARCHY ANCS AGG

PERFORM Short Reference   PERFORM Short Reference  
This documentation is copyright by SAP AG.

SELECT, hierarchy_ancestors_aggregate

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

Additions

1. ... START WHERE sql_cond

2. ... WHERE sql_cond

Effect

Specifies the hierarchy aggregate navigator HIERARCHY_ANCESTORS_AGGREGATE as the 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 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.

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

• If the hierarchy navigator HIERARCHY_ANCESTORS_AGGREGATE is used, the syntax check is performed in strict mode from Release .

See Hierarchy Navigator HIERARCHY_ANCESTORS_AGGREGATE

Addition 1

... START WHERE sql_cond

Effect

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.

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.
  
  

Addition 2

... WHERE sql_cond

Effect

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.

Note

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.

ROGBILLS - Synchronize billing plans   CPI1466 during Backup  
This documentation is copyright by SAP AG.

Length: 10464 Date: 20240419 Time: 221349     sap01-206 ( 140 ms )