We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!
... WITH HIERARCHY hierarchy
$| (hierarchy_syntax)
The addition WITH HIERARCHY exposes a common table expression as a CTE hierarchy. Under its name, a common table expression like this can be used in the subsequent queries of the current WITH statement as an SQL hierarchy hierarchy.
Only a single data source can be accessed in the subquery of the common table expression and this data source must be an SQL hierarchy hierarchy. The SQL hierarchy used as the data source of the subquery can be specified either statically or dynamically after HIERARCHY.
The result set of a common table expression exposed as a CTE hierarchy includes the hierarchy columns of the SQL hierarchy specified in the subquery. The SELECT list of the subquery must not contain any columns with the same name or alias name as a hierarchy column.
If hierarchy_syntax is specified dynamically, it is possible to decide at runtime whether a common table expression is exposed as a CTE hierarchy.
This excerpt from the program DEMO_HIERARCHY_CTE accesses CTE hierarchies in two hierarchy navigators HIERARCHY_DESCENDANTS joined in the main query of a WITH statement using a left outer join. The first CTE hierarchy represents the CDS hierarchy DEMO_CDS_PARENT_CHILD, which is accessed in its subquery. The second CTE hierarchy represents the result set of the hierarchy generator HIERARCHY, which is used in its subquery. The ON condition of the join compares two hierarchy columns of the CTE hierarchies. When executed, this program demonstrates the result.
Leave us your contact details and we will call you back. Fields marked with * are mandatory.
We offer holistic SAP solutions from a single source to shape digital change and develop new business areas.
Switzerland
Schaffhausen
Germany
Mannheim, Düsseldorf, Munich
USA
Haverhill
Greece
Thessaloniki