SAP WITH HIERARCHY ABAP Statements



Get Example source ABAP code based on a different SAP table
  


• WITH HIERARCHY WITH

WITH, HIERARCHY

ABAP_SYNTAX
... WITH HIERARCHY hierarchy
$| (hierarchy_syntax)

What does it do?
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.



Latest notes:
If hierarchy_syntax is specified dynamically, it is possible to decide at runtime whether a common table expression is exposed as a CTE hierarchy.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
This excerpt from the class CL_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. Executing CL_DEMO_HIERARCHY_CTE shows the result.
ABEXA 00774
ABAP_EXAMPLE_END

Return to menu