Kontakt

Ansicht
Dokumentation

ABENSELECT_HIERARCHY_GENERATOR - SELECT HIERARCHY GENERATOR

ABENSELECT_HIERARCHY_GENERATOR - SELECT HIERARCHY GENERATOR

ROGBILLS - Synchronize billing plans   Vendor Master (General Section)  
This documentation is copyright by SAP AG.
SAP E-Book

SELECT, FROM HIERARCHY

... HIERARCHY( SOURCE hierarchy_source $[WITH PRIVILEGED ACCESS$]
               CHILD TO PARENT ASSOCIATION _hierarchy_assoc
              $[PERIOD FROM field1 TO field2 VALID FROM from TO to$]
               START WHERE sql_cond
              $[SIBLINGS ORDER BY field1 $[ASCENDING$|DESCENDING$]$[,
                                 field2 $[ASCENDING$|DESCENDING$], ...$]$]
              $[DEPTH depth$]
              $[MULTIPLE PARENTS ${NOT ALLOWED$}${LEAVES ONLY$}$|ALLOWED$]
              $[ORPHANS IGNORE$|ERROR$|ROOT$]
              $[CYCLES ERROR$|BREAKUP$]
              $[LOAD BULK$|INCREMENTAL$|load_option$]
              $[GENERATE SPANTREE$] ) ...

Additions

1. ... SOURCE hierarchy_source $[WITH PRIVILEGED ACCESS$]

2. ... CHILD TO PARENT ASSOCIATION _hierarchy_assoc

3. ... PERIOD FROM field1 TO field2 VALID FROM from TO to

4. ... START WHERE sql_cond

5. ... SIBLINGS ORDER BY field1 $[ASCENDING$|DESCENDING$], ...

6. ... DEPTH depth

7. ... MULTIPLE PARENTS ${NOT ALLOWED$}$|${LEAVES ONLY$}$|ALLOWED

8. ... ORPHANS IGNORE$|ERROR$|ROOT

9. ... CYCLES ERROR$|BREAKUP

10. ... LOAD BULK$|INCREMENTAL$|load_option

11. ... GENERATE SPANTREE

Effect

Specifies the hierarchy generator HIERARCHY as an SQL hierarchy hierarchy in an query. The hierarchy generator creates the SQL hierarchy. The following needs to be specified:

  • The hierarchy source hierarchy_source of the SQL hierarchy must be specified after SOURCE.
  • START WHERE must be followed by a start condition that defines root nodes for the root node set of the SQL hierarchy. The SQL hierarchy consists of the root nodes of the root node set and their descendant nodes.

All other additions are optional and define further properties of the SQL hierarchy. The rows of the tabular result set of the hierarchy generator are the hierarchy nodes of the generated SQL hierarchy and their columns are composed of the elements of the source specified after SOURCE and the additional hierarchy columns.

The hierarchy generator HIERARCHY exposes all associations of the source hierarchy_source specified after SOURCE implicitly and leaves its association target unchanged.

Notes

  • The order of the additions is fixed. They must be specified as shown here.
  • Rows of the result set of the source specified after SOURCE can occur more than once in the result set of the hierarchy generator, if selected by appropriate parent-child relationships.
  • The hierarchy generator HIERARCHY in works in the same way as the statement DEFINE HIERARCHY in ABAP CDS. Accessing the hierarchy generator HIERARCHY as the data source of a query is the same as accessing a CDS hierarchy defined accordingly.
  • An association exposed implicitly by the hierarchy generator HIERARCHY can be used in path expressions or in further hierarchy generators.
  • Since hierarchies generated from the START WHERE condition are buffered on the database, it is advisable to use fixed values for the root nodes of large hierarchies of data that do not change often. To evaluate subnodes of such hierarchies, the hierarchy navigators of ABAP SQL can then be used.

Example

Specifies the hierarchy generator HIERARCHY as the data source of a SELECT statement in the program DEMO_HIERARCHY_TREE. The source of the hierarchy generator is a CDS view that uses the global temporary table DEMO_SIMPLE_TREE as a data source and whose CDS association _tree defines the parent-child relationship. The hierarchy generator selects the row with the value of start_id in the column ID as the root node and, starting from this node, inserts all descendant nodes in the result set that meet the ON condition of the hierarchy association. The result set contains all columns of the view DEMO_CDS_SIMPLE_TREE_SOURCE, plus all potential hierarchy columns. When executed, this program demonstrates the result of the SELECT statement.

Addition 1

... SOURCE hierarchy_source $[WITH PRIVILEGED ACCESS$]

Effect

The addition SOURCE specifies hierarchy_source as the hierarchy source of the hierarchy generator. This source must expose the hierarchy association _hierarchy_assoc specified after CHILD TO PARENT ASSOCIATION. The following are potential sources hierarchy_source:

  • A CDS view cds_view
cds_view can be specified as a CDS view that exposes the hierarchy association _hierarchy_assoc in its SELECT list.
  • A common table expression +cte
+cte can be specified as a preceding common table expression in a WITH statement that exposes the hierarchy association _hierarchy_assoc using the addition WITH ASSOCIATIONS. The self-association prerequisite can be met as follows:
  • The addition REDIRECTED is used to replace the association target of an association of a data source of the common table expression with the common table expression itself.

  • JOIN TO ONE$|MANY is used to define a CTE association as a self-association for the common table expression.

  • A nested SQL hierarchy hierarchy
The hierarchy generator HIERARCHY can use other SQL hierarchies hierarchy as a source. The following applies here:
  • A CDS hierarchy cds_hierarchy must expose the hierarchy association _hierarchy_assoc in its element list. To meet the self-association prerequisite, the association must be an additional association between the source of the CDS hierarchy and the CDS hierarchy.

  • The hierarchy generator HIERARCHY exposes all associations of its source implicitly.

  • A CTE hierarchy must use WITH ASSOCIATIONS to expose the hierarchy association _hierarchy_assoc. Furthermore, the addition REDIRECTED must be used to replace the association target of the association in such a way that the self-association prerequisite is met.

The hierarchy generator uses the result of an SQL hierarchy specified after SOURCE as a source, whereby its additional hierarchy columns are ignored. An outer hierarchy generator always adds its own hierarchy columns directly to the columns of the SQL hierarchy specified after SOURCE. Any hierarchy columns listed explicitly in the element list of a CDS hierarchy that is specified as a source, on the other hand, are part of this hierarchy's result set and are respected accordingly.

If a CDS role is assigned to a CDS view specified as a source, the source is implicitly subject to CDS access control and only nodes that meet the access conditions of the CDS role are transferred to the SQL hierarchy. When specifying the optional addition WITH PRIVILEGED ACCESS, the access control is deactivated, and the access conditions are not evaluated.

Note

SQL hierarchies should only be nested in exceptional cases.

Example

Uses a CDS view and two common table expressions as the source of the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_GENERATOR.

  • The first common table expression accesses the same CDS view and uses a path expression to expose the same hierarchy association. This expression must use REDIRECTED TO to redirect the hierarchy association to itself and hence meet the self-association requirement.
  • The second common table expression accesses the DDIC database table DEMO_SIMPLE_TREE in the same way as the CDS view and defines a separate CTE association as a self-association that is then used as a hierarchy association.

All main queries have the same result.

Example

Nesting of SQL hierarchies in the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_NESTED. When executed, this program demonstrates the result of the SELECT statements. Three tree-like SQL hierarchies that start at the row with the value 1 in the column ID are restricted to a depth of 2. From these hierarchies, further SQL hierarchies are selected that start at the row with the value 5 in the column ID. The three inner SQL hierarchies are specified as a CDS hierarchy, the hierarchy generator HIERARCHY, and as a CTE hierarchy. In the case of the CDS hierarchy and the CTE hierarchy, the hierarchy association must be verified as a self-association explicitly. The three inner SQL hierarchies have the same result sets, which means that the results of the three SELECT statements are also the same.

Addition 2

... CHILD TO PARENT ASSOCIATION _hierarchy_assoc

Effect

The addition CHILD TO PARENT ASSOCIATION specifies the hierarchy association, whose ON condition of the hierarchy generator selects the descendant nodes of the root node set. The hierarchy association must be exposed by the source hierarchy_source specified after SOURCE.

The hierarchy association defines the parent-child relationship between the hierarchy nodes. The following conditions apply here:

  • Only equality comparisons with the operator = and combined using AND can occur in the ON condition of the association.
  • The association source of the association cannot contain any fields that have the same name as a hierarchy column. An alternative element name must be defined for these fields.

Each row of the result set of the source hierarchy_source that meets the ON condition for an existing hierarchy node is included recursively in the SQL hierarchy as its child node, if possible.

Note

The optional additions define further conditions specifying whether a row can be included as a hierarchy node or not.

Example

The following CDS view exposes its CDS association _tree. This CDS association meets all requirements of a hierarchy association and can be used as such.

Addition 3

... PERIOD FROM field1 TO field2 VALID FROM from TO to

Effect

Defines an SQL hierarchy as a temporal SQL hierarchy in which the hierarchy nodes are limited by an adjustment of time intervals.

  • With field1 and field2, the fields of the source hierarchy_source are specified, which define the lower and upper limits of a period in the hierarchy details. field1 and field2 must be different fields of the same data type. This can be:
  • The built-in type DATS of the ABAP Dictionary.

A temporal SQL hierarchy is created as follows:

  • Only root nodes of the root node set in which the period defined using field1 and field2 has a non empty intersection with the time interval defined by from and to are respected. This intersection forms the validity interval of the root node.
  • Only child nodes in which the period defined by field1 and field2 has a non empty intersection with the validity interval of the parent node are generated. This intersection forms the validity interval of the child node.

For temporal SQL hierarchies, there are additional hierarchy columns VALID_FROM and VALID_UNTIL that contain the interval limits of the validity interval of each hierarchy node.

The addition PERIOD must not be used with GENERATE SPANTREE.

Notes

  • The validity interval of a descendant node is always a subset of a validity interval of all ancestor nodes. Validity intervals can only remain the same or become narrower from hierarchy level to hierarchy level, they never widen.
  • For a descendant node to belong to a temporal SQL hierarchy, it is not sufficient for its period to overlap with the time interval defined by from and to. Only the validity interval of the parent node is decisive. A path of a regular SQL hierarchy is truncated in a temporal SQL hierarchy at the position in which there is no intersection between the period and the preceding validity interval.
  • The value of to can also be less than the value of from. However, a validity interval is formed where necessary. In contrast, if the value of the lower interval limit of the period is greater than the value of the upper interval limit, the validity interval is empty.
  • Additions such as MULTIPLE PARENTS or CYCLES affect the temporal SQL hierarchy. Nodes that would raise an exception in a regular SQL hierarchy can be hidden in a temporal SQL hierarchy.
  • On an SAP HANA database, the associated hierarchy generator function HIERARCHY_TEMPORAL is used to create a temporal SQL hierarchy.

Example

Creates two temporal SQL hierarchies in the program DEMO_HIERARCHY_TEMPORAL, where in one date fields and in the other time stamp fields are used as periods.

When executed, this program demonstrates how the PERIOD addition works. The date and time stamp fields of the DDIC database table DEMO_PARCHLD_PRD, which are accessed in the source of the hierarchy generator DEMO_CDS_PARENT_CHILD_SRC_PRD, are filled with data for this purpose. A regular SQL hierarchy without the addition PERIOD and the two temporal SQL hierarchies are shown. The validity intervals are also output for these.

  • The temporal SQL hierarchy based on date fields contains only the nodes with the values A, B, and C in the column ID. The period of the node with the value D does not overlap with the validity interval of the parent node with the value B. This means that the child node with the value E is truncated, regardless of its period. All other nodes F to I haven empty periods and are not relevant anyway.
  • The temporal SQL hierarchy based on time stamps contains all nodes with periods TS_FROM to TS_TO that are not empty. The example illustrates how the validity intervals with the hierarchy levels can become narrower. In the last row, the validity interval corresponds to the previous one, even though the period is wider.

Addition 4

... START WHERE sql_cond.

Effect

The addition START WHERE specifies the start condition for the hierarchy generator. After START WHERE, a logical expression sql_cond must be specified that selects rows from the source hierarchy_source. The selected rows are inserted into the SQL hierarchy as a root node set. For each root node in the root node set, the descendant nodes are selected that meet the ON condition of the hierarchy association and, if possible, inserted into the SQL hierarchy.

Note

The start condition should select a meaningful set of root nodes. If no rows in the result set of the source hierarchy_source meet the condition, the SQL hierarchy is empty. If all rows meet the condition, the descendant nodes of every row are selected and inserted.

Example

Specifies an interval condition for the start condition of the hierarchy generator HIERARCHY in the program DEMO_HIERARCHY_START_WHERE. All rows in the CDS view specified as the source that meet the condition are inserted in the SQL hierarchy as root nodes and all their descendant nodes are selected. When executed, this program allows different limits to be entered and displays the result.

Addition 5

... SIBLINGS ORDER BY field1 $[ASCENDING$|DESCENDING$], ...

Effect

The addition SIBLINGS ORDER BY sorts sibling nodes in the SQL hierarchy generated by the hierarchy generator. If this addition is not used, the order of the sibling nodes is undefined.

Fields field1, field2, ... of the source cds_view can be specified in a comma-separated list after the addition hierarchy_source to specify the order of the sibling nodes.

The addition ASCENDING or DESCENDING can be specified for each field to specify an ascending or descending order, whereby ascending is the default.

The fields specified after ORDER BY cannot be of the type LCHR, LRAW, STRING, RAWSTRING, or GEOM_EWKB.

Notes

  • If additionally a general ORDER BY clause is specified in current query, it affects the SQL hierarchy that is sorted by SIBLINGS ORDER BY.
  • The additional hierarchy columns cannot be specified after SIBLINGS ORDER BY. They can, however, be specified in the general ORDER BY clause of the current query.

Example

Ascending and descending sort of sibling nodes in the program DEMO_HIERARCHY_SIBLINGS_ORDER. When executed, this program demonstrates how these additions work.

Addition 6

... DEPTH depth

Effect

The addition depth can be used to limit the number of hierarchy levels used to create descendant nodes. depth expects a host variable, a host expression, or a literal of type i. Only the types b, s, or i can be specified for depth.

The value in depth has the following meaning:

  • For depth values greater than 0, as many hierarchy edges as specified in depth are traced, starting from a root node.
  • If the value of depth is 0, only the root nodes are inserted into the SQL hierarchy.
  • If depth is less than 0, no hierarchy nodes are created and sy-subrc is set to 4.

The addition DEPTH can be used only if the addition ORPHANS is not specified or is specified as ORPHANS IGNORE.

Example

Limits the number of hierarchy levels in the program DEMO_HIERARCHY_DEPTH. When executed, this program demonstrates how this addition works. It also shows the hierarchy level from the hierarchy column HIERARCHY_LEVEL.

Addition 7

... MULTIPLE PARENTS ${NOT ALLOWED$}$|${LEAVES ONLY$}$|ALLOWED

Effect

The addition MULTIPLE PARENTS can be used to define whether the hierarchy generator is allowed to generate child nodes with multiple parent nodes:

  • NOT ALLOWED
This is the default setting. A child node can have exactly on parent node only).
  • LEAVES ONLY
Only leaf nodes can have multiple parent nodes.
  • ALLOWED
All hierarchy nodes can have multiple parent nodes.

Example

Creation of an SQL hierarchy in the program DEMO_HIERARCHY_MULTI_PARENTS. Only leaf nodes can have multiple parents here.

When executed, this program demonstrates how the MULTIPLE PARENTS addition works.

  • The first SELECT statement does not specify the addition and NOT ALLOWED is applied implicitly. The hierarchy node with the ID D has three parent nodes and hence an exception is raised.
  • The next SELECT statement specifies the addition MULTIPLE PARENTS LEAVES ONLY. The hierarchy node with the ID D is a leaf node, which means the function can be accessed.
  • A child node for the row with the ID D is inserted in front of the next SELECT statement in the DDIC database table DEMO_PARENT_CHLD. If used, the addition MULTIPLE PARENTS LEAVES ONLY now also raises an exception.
  • The next SELECT statement specifies the addition MULTIPLE PARENTS ALLOWED and it is possible to access the table again.

Addition 8

... ORPHANS IGNORE$|ERROR$|ROOT

Effect

The addition ORPHANS defines how orphan nodes are handled. The following categories of orphan nodes exist:

  • Hierarchy nodes that could have parent nodes according to the parent-child relationship, but the parent nodes are not in the SQL hierarchy (true orphans).
  • Hierarchy nodes that cannot be reached from the root node set using hierarchy edges.
  • Hierarchy nodes that are part of a node cycle and cannot be reached from the root node set using hierarchy nodes (island orphans).

The additions work as follows:

  • IGNORE
This is the default setting. Any orphan nodes are not inserted in the SQL hierarchy.
  • ERROR
Any orphan nodes detected raise an exception.
  • ROOT
Orphan nodes are inserted into the SQL hierarchy as follows:
  • True orphans are included in the root node set as root nodes and flagged as orphan nodes in the hierarchy column HIERARCHY_IS_ORPHAN.

  • Descendant nodes of true orphans are handled like those of parent nodes from the root node set, but are also flagged as orphan nodes in the hierarchy column HIERARCHY_IS_ORPHAN.

  • For the hierarchy nodes of island orphans, a parent node in the root node set is generated for the child node where the cycle occurs. In the generated root node, all columns of the source hierarchy_source contain the null value. In the hierarchy columns, the additional root node is flagged as an orphan node and PARENT_ID also contains the null value.

Example

Handles orphan nodes in the program DEMO_HIERARCHY_ORPHANS.

When executed, this program demonstrates how the ORPHANS addition works.

  • The first SELECT statement specifies the addition ORPHANS IGNORE. All orphan nodes are ignored and only the root node set consisting of a single root node is read and inserted in the SQL hierarchy.
  • The next SELECT statement specifies the addition ORPHANS ERROR. The SQL hierarchy would contain orphan nodes and hence an exception is raised.
  • The next SELECT statement specifies the addition ORPHANS ROOT and all orphan nodes are inserted in the SQL hierarchy:
  • The hierarchy node with the value B in the column ID is a true orphan node. It contains a blank in the column PARENT_ID but there is no root node with a blank in the column ID. The addition ROOT includes the hierarchy node itself in the root node set (the hierarchy column HIERARCHY_PARENT_RANK has the value 0) and the row is flagged as an orphan node in the hierarchy column HIERARCHY_IS_ORPHAN.

  • The hierarchy node with the value C in the column ID contains the value B in the column PARENT_ID. The parent node is an orphan node included in the root node set, which means that this hierarchy node is also flagged as an orphan node.

  • The hierarchy node with the value X in the column ID also contains the value X in the column PARENT_ID. The hierarchy node therefore starts a node cycle where this node is itself the parent node. It is not part of the root node set, however, which means it is an island orphan. A parent node is inserted into the root node set in which the columns of the CDS view DEMO_CDS_PARENT_CHILD_SOURCE and the hierarchy column PARENT_ID, contain null values.

  • The hierarchy nodes with the values U, V, and W in the column ID also comprise an island orphan. None of the hierarchy nodes in this node cycle is a root node. Again, a parent node is inserted in the root node set in which the columns of the CDS view and the hierarchy column PARENT_ID, contain null values.

Addition 9

... CYCLES ERROR$|BREAKUP

Effect

The addition CYCLES defines how node cycles are defined. The additions work as follows:

  • ERROR
This is the default setting. An exception is raised when a node cycle is detected.
  • BREAKUP
The tracing of descendant nodes is terminated at the child node where the node cycle occurs and the hierarchy column HIERARCHY_IS_CYCLE is set to the value 1.

If the addition BREAKUP is specified, MULTIPLE PARENTS ALLOWED must also be specified.

Example

Handling of node cycles in the program DEMO_HIERARCHY_CYCLES.

When executed, this program demonstrates how the CYCLES addition works.

  • The first SELECT statement specifies the addition CYCLES BREAKUP. Here, the root node set consists of four root nodes of the CDS view DEMO_CDS_PARENT_CHILD_SOURCE and the following node cycles are detected:
  • The child node with the value A in the column ID is also its parent node. The hierarchy column HIERARCHY_IS_CYCLE contains the value 1 and no more descendant nodes are created.

  • Starting from the hierarchy node with the value X in the column ID, child nodes are created with the values Y and Z and again X. The cycle is detected at the final child node, HIERARCHY_IS_CYCLE contains the value 1, and no more descendant nodes are created. The same applies to the descendant nodes starting from the root nodes with the values Y and Z.

  • The second SELECT statement specifies the addition CYCLES ERROR and raises an exception.

Addition 10

... LOAD BULK$|INCREMENTAL$|load_option

Effect

The addition LOAD specifies the load policy for the generated hierarchy. It can be used for performance optimization.

The addition works as follows:

  • BULK
This is the default setting. The entire source table of the hierarchy is loaded.
  • INCREMENTAL
Only the rows of the source table that can be reached from the start nodes (root node set) are loaded.
  • load_option
load_option is an enumerated object with the enumerated type LOAD_OPTION from the class SQL_HIERARCHY and the following enumerated constants can be passed:
  • SQL_HIERARCHY=>C_LOAD_OPTION-BULK: equivalent to LOAD BULK.

  • SQL_HIERARCHY=>C_LOAD_OPTION-INCREMENTAL: equivalent to LOAD INCREMENTAL.

Notes

  • The performance optimization with LOAD INCREMENTAL depends on the data source. If the source table is very large and the hierarchy generator reads relatively few rows, there's a positive effect. If, on the other hand, the source table has only few rows and they are all part of the hierarchy, LOAD INCREMENTAL might even take longer than LOAD BULK.
  • LOAD INCREMENTAL should not be used with global temporary tables as data source, because it does not have any positive effect then (if anything, it slows down the performance).

The executable example demonstrates how the addition LOAD INCREMENTAL can speed up the generation of a hierarchy.

BULK vs INCREMENTAL

Addition 11

... GENERATE SPANTREE

Effect

If the addition GENERATE SPANTREE is specified, the hierarchy generator inserts only child nodes without multiple parent nodes, starting from every root node. If, due to its parent-child relationships, a child node were to have multiple parent nodes after its root node, exactly one of the possible paths from the root node to this child node is selected and the child node is created for this path only.

  • If the paths have different lengths, the shortest is selected.
  • If the paths all have the same length, the first path found is selected.

If the addition GENERATE SPANTREE is specified, the following additions must be specified at the same time:

  • MULTIPLE PARENTS ALLOWED
  • ORPHANS IGNORE or ORPHANS not specified
  • CYCLES BREAKUP

Notes

  • If the parent-child relationships for the current data produce only tree-like SQL hierarchies, the addition GENERATE SPANTREE is ignored.
  • Selecting one of multiple paths to a child node does not mean that all others are completely discarded. In this case, only the edges leading to the child node are missing.
  • The addition GENERATE SPANTREE can be used to detect whether at least one path leads from a root node to a child node without the result set needing to contain all paths.
  • The addition GENERATE SPANTREE is used in an SAP HANA database to access the hierarchy generator function HIERARCHY_SPANTREE there.

Example

Use of GENERATE SPANTREE in the program DEMO_HIERARCHY_SPANTREE.

When executed, this program demonstrates how the GENERATE SPANTREE addition works.

  • The first SELECT statement does not specify the addition GENERATE SPANTREE. The SQL hierarchy contains all possible paths from root nodes to child nodes.
  • The second SELECT statement specifies the addition GENERATE SPANTREE. Starting from each root node, the result set only contains one path to the potential child nodes:
  • From the root node with the value A in ID, two paths lead to D. Only the shorter path directly from A to D is created. The connection between C and D in the longer path is not created.

  • From the root node with the value A in ID, two paths lead to N. Only one of the two paths from L to N or M to N is created.

  • The child node with the value Z in ID has two parent nodes X and Y. Since both are root nodes, both paths are created.






General Material Data   General Material Data  
This documentation is copyright by SAP AG.

Length: 53589 Date: 20240419 Time: 222415     sap01-206 ( 679 ms )