Simplify select using hierarchy function

Question

I wonder if it could be possible to simplify the following ABAP-based TDEVC selection using the new hierarchy functions of ABAP SQL.

Table TDEVC is SAP's table of packages (= development classes), with the columns (DEVCLASS,PARENTCL) definig the hierarchy.

The goal is to list each package in the custom namespace, whose name starts with the letter Z, together with its parent package and its root package (the uniquely defined ancestor with PARENTCL = ''.

This would be the selection without hierarchy, performed with plain old ABAP. I didn't succeed to define TDEVC as a hierarchy source in the SELECT statement itself, as described in the ABAP documentation.

types:
  begin of ty_node,
    devclass type devclass,
    parentcl type devclass,
    root     type devclass,
  end of ty_node,
  ty_nodes type sorted table of ty_node
    with non-unique key parentcl.

data:
  lt_nodes type ty_nodes,
  lt_root type ty_nodes,
  lt_parents type ty_nodes.

field-symbols:
  <ls_node> type ty_node,
  <ls_parent> type ty_node.

select devclass, parentcl from tdevc
  into table @lt_nodes
  where devclass like 'Z%'.

loop at lt_nodes assigning <ls_node>
  where parentcl eq ''.
  insert value #(
    devclass = <ls_node>-devclass
    root     = <ls_node>-devclass )
    into table lt_root.
  delete lt_nodes.
endloop.

lt_parents = lt_root.
while lt_parents is not initial.
  data(lt_parents_new) = value ty_nodes(  ).
  loop at lt_parents assigning <ls_parent>.
    loop at lt_nodes assigning <ls_node>
      where parentcl = <ls_parent>-devclass.
      <ls_node>-root = <ls_parent>-root.
      insert <ls_node> into table lt_parents_new.
    endloop.
  endloop.
  lt_parents = lt_parents_new.
endwhile.
* Result is now in lt_nodes

Answer 1

It is possible to a make a selection using hierarchy functionality in the following way:

WITH
  +tdevc_source AS
  ( SELECT FROM tdevc
      FIELDS devclass AS id, parentcl AS parent, parentcl AS root
      WHERE devclass LIKE 'Z%' )
    WITH ASSOCIATIONS
  ( JOIN TO MANY +tdevc_source AS _tdevc ON +tdevc_source~parent = _tdevc~id ),


  +tdevc_hierarchy AS
  ( SELECT FROM HIERARCHY( SOURCE +tdevc_source
                           CHILD TO PARENT ASSOCIATION _tdevc
                           START WHERE parent IS INITIAL
                           SIBLINGS ORDER BY id
                           MULTIPLE PARENTS NOT ALLOWED
                          ) AS cte_hierarchy
    FIELDS id, parent, root )
    WITH HIERARCHY cte_hierarchy


   SELECT FROM +tdevc_hierarchy
     FIELDS
       id, parent, root,
       hierarchy_rank, hierarchy_parent_rank
     INTO TABLE @DATA(lt_nodes).

We use common table expressions (CTEs) to define the internal ABAP SQL definition of data sources.

Additionally two extra fields hierarchy_rank and hierarchy_parent_rank are selected, which reflect the hierarchical relations between records.

The requirement says, that the result should contain different hierarchy level objects in every single row (up to a root package), so the final processing of the internal result table is necessary to achieve this, for example:

LOOP AT lt_nodes ASSIGNING FIELD-SYMBOL(<fs_node>).
  DATA(lv_hpr) = <fs_node>-hierarchy_parent_rank.
  WHILE lv_hpr > 0.
    ASSIGN lt_nodes[ hierarchy_rank = lv_hpr ] TO FIELD-SYMBOL(<fs_node_root>).
    lv_hpr = <fs_node_root>-hierarchy_parent_rank.
    IF lv_hpr = 0.
      <fs_node>-root = <fs_node_root>-id.
    ENDIF.
  ENDWHILE.
ENDLOOP.
DELETE lt_nodes WHERE hierarchy_parent_rank = 0.