SAP SELECT HIERARCHY GENERATOR Get Example source ABAP code based on a different SAP tableSAP Help
• HIERARCHY ABAP_OSQL_TABLE_FUNC
• SOURCE HIERARCHY
• WITH PRIVILEGED ACCESS HIERARCHY
• CHILD TO PARENT ASSOCIATION HIERARCHY
• PERIOD FROM TO HIERARCHY
• VALID FROM TO HIERARCHY
• START WHERE HIERARCHY
• LOAD BULK HIERARCHY
• LOAD INCREMENTAL HIERARCHY
• SIBLINGS ORDER BY HIERARCHY
• ASCENDING HIERARCHY
• DESCENDING HIERARCHY
• DEPTH HIERARCHY
• MULTIPLE PARENTS ALLOWED HIERARCHY
• MULTIPLE PARENTS NOT ALLOWED HIERARCHY
• MULTIPLE PARENTS LEAVES ONLY HIERARCHY
• ORPHANS ERROR HIERARCHY
• ORPHANS IGNORE HIERARCHY
• ORPHANS ROOT HIERARCHY
• CYCLES ERROR HIERARCHY
• CYCLES BREAKUP HIERARCHY
• GENERATE SPANTREE HIERARCHY
SELECT, FROM HIERARCHY ABAP_SYNTAX ... HIERARCHY( SOURCE hierarchy_source $[WITH PRIVILEGED ACCESS$] sql_cond ABAP Addition ... 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$], ... ... DEPTH depth ... MULTIPLE PARENTS ${NOT ALLOWED$}$|${LEAVES ONLY$}$|ALLOWED ... ORPHANS IGNORE$|ERROR$|ROOT ... CYCLES ERROR$|BREAKUP ... LOAD BULK$|INCREMENTAL$|load_option ... GENERATE SPANTREE What does it do? hierarchy generator HIERARCHY as an SQL hierarchy hierarchy in an AB_SQL 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. After CHILD TO PARENT ASSOCIATION, a hierarchy association _hierarchy_assoc exposed by the source hierarchy_source must be specified. The association source and association target of this association must be the source hierarchy_source. The ON condition of the hierarchy association defines the parent-child relationships between the hierarchy nodes. 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. 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. HIERARCHY exposes all associations of the source hierarchy_source specified after SOURCE implicitly and leaves its association target unchanged. Latest 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 AB_SQL 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. 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. An association exposed implicitly by the hierarchy generator HIERARCHY can be used in path expressions or in further hierarchy generators. ABAP_EXAMPLE_VX5 HIERARCHY as the data source of a SELECT statement in class CL_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. Executing CL_DEMO_HIERARCHY_TREE demonstrates the result of the SELECT statement. ABAP Addition What does it do? 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 cardinality 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. 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. Latest notes: AS tabalias behind hierarchy_source, but it does not work. ABAP_EXAMPLE_VX5 HIERARCHY in the class CL_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. ABAP_EXAMPLE_VX5 HIERARCHY in the class CL_DEMO_HIERARCHY_NESTED . When executed, CL_DEMO_HIERARCHY_NESTED 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. ABAP Addition What does it do? 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. hierarchy nodes. The following conditions apply here: The association must be a self-association. Only equality comparisons with the operator = and combined using AND can occur in the ON condition of the association. In each comparison in the ON condition, one field of the association source must be compared with a field, which is prefixed with _hierarchy_assoc, of the association target. 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. 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. Latest notes: ABAP_EXAMPLE_VX5 _tree. This CDS association meets all requirements of a hierarchy association and can be used as such. ABAP Addition What does it do? 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 data type that is defined by one of the DDIC data elements TIMESTAMP or TIMESTAMPL. from and to define the lower and upper limit of a time interval that acts as a condition for the periods of the root node set. For from and to, host variables, host expressions, and type-compliant literals can be specified, whose ABAP data type matches the dictionary data type of field1 and field2 exactly. DATN and UTCLONG are not yet supported. 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. hierarchy columns VALID_FROM and VALID_UNTIL that contain the interval limits of the validity interval of each hierarchy node. PERIOD must not be used with GENERATE SPANTREE . Latest 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 association source of the current hierarchy association must not have any fields called VALID_FROM or VALID_UNTIL. An alternative element name must be defined for these fields. 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. ABAP_EXAMPLE_VX5 temporal SQL hierarchies in the class 