Skip to Content
Technical Articles

ABAP CDS recursive association using hierarchy

Introduction

In this article, I will show a practical example of self-association in CDS. You will learn how to define a simple hierarchy in your CDS and how to consume it in your ABAP code.

The problem

Recently I have faced a quite interesting problem. There is a concept of something like ‘frame contract’. It means that out of this document many others of the same type (or not necessairely) can be created. Let’s take a look at VBFA table (Sales Document Flow). I am interested only in subsequent Sales Orders, therefore I have filtered out only documents of category ‘C’:

What we can see is a ‘chain’ of documents, or even to be more specific – a tree. Let’s take a look at document 24. More friendly and eye-catching representation of the relationship between those documents would be:

Root parent of the above tree would be document 24 – our frame contract. Below it you will see it’s ‘children’ – subsequent documents. Now imagine that you would like to extract all documents related to your frame contract. Lot’s of twisted ABAP! Maybe there is an easier way of solving this riddle?

Gentle solutions

Yes! Fortunately, there is – we can avoid looping, reading, assigning and dumping.

CDS

We can define a CDS view which would have an association with itself. Let’s take a look:

define view Z_VBFA
  as select from vbfa
  association [0..1] to Z_VBFA as _Recursion on $projection.PrecedingDocument = _Recursion.SubsequentDocument
                                             and $projection.PrecedingItem = _Recursion.SubsequentItem
{
  key vbelv   as PrecedingDocument,
  key posnv   as PrecedingItem,
      vbeln   as SubsequentDocument,
      posnn   as SubsequentItem,
      vbtyp_n as SubsequentDocCategory,
      _Recursion
}
where
  vbtyp_n = 'C'

I have defined a view Z_VBFA based on VBFA and added an association to Z_VBFA. As you can see preceding documents will be treated as parents and subsequent ones as children. The cardinality of the above association is [0..1] as there cannot be any multiple parents – one contract can only have one preceding document, however it can have multiple children. In order to allow consumption of such CDS annotation needs to be added:

@Hierarchy.parentChild: [ { name: 'Recursion', recurseBy: '_Recursion' } ]

It defines parent-child hierarchy and points at our self-association. There is also a second way of defining a hierarchy:

@Hierarchy.parentChild: { name: 'Recursion',
                          recurse : { parent: ['PrecedingDocument','PrecedingItem'], child ['SubsequentDocument','SubsequentItem'] }
}

I believe that the first one is much nicer. Remember that in that approach you should not use association.

Hierarchy

You can also define a hierarchy object Z_VBFA_HIERARCHY which would look like this:

define hierarchy Z_VBFA_HIERARCHY 
  as parent child hierarchy (
    source Z_VBFA
    child to parent association _Recursion
    siblings order by PrecedingDocument ascending, PrecedingItem ascending
    orphans root
  )
{
    key PrecedingDocument,
    key PrecedingItem,
    SubsequentDocument,
    SubsequentItem,
    $node.parent_id as ParentNode,
    $node.node_id as ChildNode,
    $node.hierarchy_is_orphan as HierarchyIsOrphan,
    $node.hierarchy_level as HierarchyLevel,
    $node.hierarchy_rank as HierarchyRank,
    $node.hierarchy_parent_rank as HierarchyParentRank,
    $node.hierarchy_tree_size as HierarchyTreeSize  
}
  • Source – Z_VBFA – previously defined view with self-association
  • Child to parent association – points at recursion association in the source
  • Siblings order – order of sibling nodes
  • Orphans root – orphans will be treated as roots. In our case contract 24 would be such node.
  • Multiple parents – this syntax is not used here as multiple parents are not allowed in my case

I have also listed out all $node fields. These are hierarchy-specific fields describing a hierarchy. We will take a look at them later. After running the hierarchy you will see a standard table view:

Gentle consumption

Now let’s take advantage of the work we have done here and let’s consume our objects in ABAP. There are few useful HIERARCHY functions – you can read more about them here:

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.01/en-US/fc59f81a5c494f399cc2ff70b9c3b4c9.html.

We will Focus on three of them:

  1. HIERARCHY – generates a hierarchy
  2. HIERARCHY_DESCENDANTS – Returns all descendants of a set of start nodes in a hierarchy.
  3. HIERARCHY_ANCESTORS – Returns all ancestors of a set of start nodes in a hierarchy.

Let’s implement it in ABAP.

HIERARCHY

A hierarchy from CDS view with defined hierarchy will be generated.

SELECT parent_id AS parent,
         node_id AS node,
         hierarchy_level AS level,
         hierarchy_rank AS rank,
         hierarchy_tree_size AS tree_size
    FROM HIERARCHY( SOURCE z_vbfa
                    CHILD TO PARENT ASSOCIATION _recursion
                    START WHERE precedingdocument = '0000000024' AND precedingitem = '000000'
                    SIBLINGS ORDER BY precedingdocument ASCENDING, precedingitem ASCENDING )
    INTO TABLE @DATA(lt_hierarchy).

Selected fields are hierarchy nodes that are always available and describe the structure. You need to select them explicitly and add an alias. Let’s take a look at the SOURCE:

  • CHILD TO PARENT ASSOCIATION – points at recursive association in the view
  • START WHERE – points at a starting node in the hierarchy

Result:

INDEX PARENT NODE LEVEL RANK TREE_SIZE
1 10,0000000024;6,000000 10,0000000025;6,000000 1 1 11
2 10,0000000025;6,000000 10,0000000067;6,000000 2 2 1
3 10,0000000025;6,000000 10,0000000061;6,000000 2 3 1
4 10,0000000025;6,000000 10,0000000060;6,000000 2 4 1
5 10,0000000025;6,000000 10,0000000063;6,000000 2 5 1
6 10,0000000025;6,000000 10,0000000064;6,000000 2 6 1
7 10,0000000025;6,000000 10,0000000065;6,000000 2 7 1
8 10,0000000025;6,000000 10,0000000066;6,000000 2 8 1
9 10,0000000025;6,000000 10,0000000068;6,000000 2 9 3
10 10,0000000068;6,000000 10,0000000069;6,000000 3 10 1
11 10,0000000068;6,000000 10,0000000070;6,000000 3 11 1

We get all relationships between nodes, starting from contract 24. What’s great all children are listed out in the node column, so our goal has been achieved. Let’s dig deeper into it however and take a look at hierarchy specific fields which may help us with traversing a hierarchy. Below pictures explain two of them:

HIERARCHY_DESCENDANTS

In this example however we are going to consume our CDS hierarchy directly.

  SELECT *
    FROM HIERARCHY_DESCENDANTS( SOURCE z_vbfa_hierarchy
                                START WHERE precedingdocument = '0000000024' 
                                        AND precedingitem = '000000' )
    INTO TABLE @DATA(lt_descendants).

Result:

INDEX

PRECEDING

DOCUMENT

PRECEDING

ITEM

SUBSEQUENT

DOCUMENT

SUBSEQUENT

ITEM

PARENT

NODE

CHILD

NODE

HIERARCHY

ISORPHAN

HIERARCHY

LEVEL

HIERARCHY

RANK

HIERARCHY

PARENTRANK

HIERARCHY

TREESIZE

1 0000000024 0 0000000025 0 10,0000000024;6,000000 10,0000000025;6,000000 1 1 2 0 11
2 0000000025 0 0000000067 0 10,0000000025;6,000000 10,0000000067;6,000000 1 2 3 2 1
3 0000000025 0 0000000061 0 10,0000000025;6,000000 10,0000000061;6,000000 1 2 4 2 1
4 0000000025 0 0000000060 0 10,0000000025;6,000000 10,0000000060;6,000000 1 2 5 2 1
5 0000000025 0 0000000063 0 10,0000000025;6,000000 10,0000000063;6,000000 1 2 6 2 1
6 0000000025 0 0000000064 0 10,0000000025;6,000000 10,0000000064;6,000000 1 2 7 2 1
7 0000000025 0 0000000065 0 10,0000000025;6,000000 10,0000000065;6,000000 1 2 8 2 1
8 0000000025 0 0000000066 0 10,0000000025;6,000000 10,0000000066;6,000000 1 2 9 2 1
9 0000000025 0 0000000068 0 10,0000000025;6,000000 10,0000000068;6,000000 1 2 10 2 3
10 0000000068 0 0000000069 0 10,0000000068;6,000000 10,0000000069;6,000000 1 3 11 10 1
11 0000000068 0 0000000070 0 10,0000000068;6,000000 10,0000000070;6,000000 1 3 12 10 1

 

I used * sign in select, therefore, all fields from defined earlier CDS hierarchy were selected. Notice that in this case Z_VBFA_HIERARCHY is consumed, not Z_VBFA.

 

HIERARCHY_ANCESTORS

This function will return the ancestors of a given node.

SELECT *
    FROM HIERARCHY_ANCESTORS( SOURCE z_vbfa_hierarchy
                              START WHERE precedingdocument = '0000000024' 
                                      AND precedingitem = '000000' )
    INTO TABLE @DATA(lt_ancestors).

Result:

INDEX

PRECEDING

DOCUMENT

PRECEDING

ITEM

SUBSEQUENT

DOCUMENT

SUBSEQUENT

ITEM

PARENTNODE CHILDNODE

HIERARCHY

ISORPHAN

HIERARCHY

LEVEL

HIERARCHY

RANK

HIERARCHY

PARENTRANK

HIERARCHY

TREESIZE

1 0000000024 0 0000000025 0 10,0000000024;6,000000 10,0000000025;6,000000 1 1 2 0 11

I have added orphans root annotation therefore one relationship in the hierarchy is displayed.

Alternative syntax

You can combine HIERARCHY and HIERARCHY_DESCENDANTS/HIERARCHY_ANCESTORS:

SELECT
    FROM HIERARCHY_DESCENDANTS( SOURCE HIERARCHY( SOURCE z_vbfa
                                                    CHILD TO PARENT ASSOCIATION _recursion
                                                    START WHERE precedingdocument = '0000000024'
                                                            AND precedingitem = '000000'
                                                    SIBLINGS ORDER BY precedingdocument ASCENDING, precedingitem ASCENDING )
    START WHERE precedingdocument = '0000000024' AND precedingitem = '000000' )
    FIELDS    node_id,
              parent_id,
              hierarchy_rank,
              hierarchy_level,
              hierarchy_tree_size,
              hierarchy_distance
    INTO TABLE @DATA(lt_descendants_2).

It will dynamically return hierarchy and apply HIERARCHY_DESCENDANTS on the result. Notice that we are not using Z_VBFA_HIERARCHY here but Z_VBFA. The result will be exactly the same as in HIERARCHY_DESCENDANTS.

Summary

I hope you can see the advantage of such solution. With few annotations and one select, we can extract the whole tree. With additional parameters like hierarchy_rank etc. it is incredibly easy to traverse through the whole structure. You can even select all other business data and have them all with one simple select. When possible avoid writing complex code!

 

6 Comments
You must be Logged on to comment or reply to a post.
  • Very well written post and a great topic.
    I’ve been waiting for someone to eventually pick up this topic and you did it in a really nice way.

    Things like hierarchy-handling seem to always linger in the back corner of the programmer’s mind and especially with SQL data the approach commonly is one of

    • read all data in and do the hierarchy stuff it in the application
    • write a recursive SQL query

    The problems with those approaches are clear. In the first case, there is a lot of unnecessary data movement which means, performance will be a lot less than optimal.

    In the second case, the problem is that all of a sudden imperative code (how to process the data) is implemented in the DB, even though SQL is a declarative language.
    Using this approach, the purpose of the query takes a ride in the backseat, while the developer needs to focus on the correct implementation of hierarchy processing.

    This is where the hierarchy functions of SAP HANA are very beneficial. One can focus on what this hierarchy is about and not how to string together the different “nodes”.
    And this is the thinking that leads me to the single proposed change in the code examples:

    The name for the self-referencing association _Recursion refers to the processing technique that is employed to go through and connect the nodes. It does not describe the relationship (association) in terms of the data model.
    Better names could be _RelatedDocuments or _LinkedDocuments as this is what the relation is about (given the bi-directional nature of the relation _ParentDocuments or similar would not work).

    Thanks again for putting this piece out for all to read.

    • Thanks for your valuable insight!
      I couldn’t find any materials apart from documentation so I decided to create one.

      Exactly, writing such code on application server would cause performance issues and probably would be complex and not easy to read.

      I like the idea of using names you gave as indeed it is not one-way relationship. I will stick to that in my further hierarchies!

  • Hi Andrzej,  I understand how to consume hierarchy object Z_VBFA_HIERARCHY in ABAP Open SQL.  I would like to know how does this CDS hierarchy object is consumed in a  #Dimension CDS view as hierarchy association?

  • Hi Andrzej,

    I am working for the hierarchy object, I defined a Hierarchy ZTEST_ORG_HIER,

    I want to implement a fuzzy search in it.   How can I do it?

    Thanks in advance.