Documentation View

We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!



RFUMSV00 - Advance Return for Tax on Sales/Purchases   Addresses (Business Address Services)  
This documentation is copyright by SAP AG.
SAP E-Book


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

  • hierarchy is used to specify either the name of the SQL hierarchy or its alias name defined using AS. When the hierarchy generator HIERARCHY is used, it must have an alias name and this name must be specified.
  • A parenthesized data object hierarchy_syntax can be specified. When the statement is executed, this data object must either contain the static syntax specifying the SQL hierarchy or be initial. In this case, the common table expression can be used only in other dynamic tokens of the WITH statement. The data object hierarchy_syntax can be a character-like data object or a standard table with a character-like row type. The syntax in hierarchy_syntax, as in the static syntax, is not case-sensitive. Invalid syntax raises a catchable exception of the class CX_SY_DYNAMIC_OSQL_ERROR. If hierarchy_syntax is initial, the common table expression is not exposed as an SQL 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.

General Data in Customer Master   ABAP Short Reference  
This documentation is copyright by SAP AG.

Length: 4574 Date: 20230207 Time: 090722     sap01-206 ( 88 ms )