Working with Hierarchies in ABAP SQL

This blog summarizes the possibilities, ABAP SQL offers together with ABAP CDS for working with hierarchical data that are stored in database tables. Hierarchical data in database tables means, that lines of one or more database tables are connected by parent-child relationships. There are many use cases, where hierarchical data play a role and where accessing information about the hierarchical relationship is important. For example, a common task can be to find out the descendants or ancestors of a given hierarchy node or to aggregate values of subtrees.

Overview

In former times you had to load the hierarchical data from the database into internal tables and program it all by yourself (if you did not find an appropriate API). In between, meshes offered some features for working with hierarchies, as shown in this example, but have not found wide distribution.

Meanwhile, the standard AS ABAP database is a SAP HANA database that offers a lot of helpful features. Among others, you find a set of hierarchy functions there, that allow you to deal with hierarchical data directly on the database and that you can look up in the SAP HANA documentation. Now you might expect that you must use AMDP in order to access these functions from your ABAP programs, but no need to do so! From release 7.53 on, ABAP SQL and ABAP CDS support hierarchies directly by wrapping the HANA built-in functions without any loss of performance. You can stay in the comfortable ABAP world and nevertheless have access to most modern features. All you have to do, is to understand some concepts and learn some additional syntax and then you can start right away.

SQL Hierarchies

With SQL hierarchy we denote a special hierarchical data source that you can use in the FROM clause of ABAP SQL queries. A SQL hierarchy is a tabular set of rows which form the hierarchy nodes of a hierarchy and which contains additionally hierarchy columns that contain hierarchy attributes with hierarchy-specific information for each row. For creating a SQL hierarchy, you need the following:

• Data Source – This can be any data source you can access normally in an ABAP SQL query, as most commonly a database table or a CDS view, but also a CTE (common table expression) or an internal table @itab. The structure and contents of the data source should be able to represent hierarchical data.
• Parent-child Relation – A parent-child relation must be defined between two or more columns of the data source. From the parent-child relationship and the actual data of the data source, the SQL hierarchy consisting of parent nodes and child nodes can be created. The parent-child relation must be defined by a self-association which we call hierarchy association. This can be achieved with CDS associations or CTE associations. A data source exposing a hierarchy association can be used as a hierarchy source for creating a SQL hierarchy.
• Hierarchy Creation – From a hierarchy source, that is a data source exposing a hierarchy association, a SQL hierarchy can be created. This can be done either by defining a CDS hierarchy outside an ABAP program or with the hierarchy generator of ABAP SQL directly in the FROM clause of an ABAP SQL query inside an ABAP program.

The following topics show you step-by-step how SQL hierarchies can be created and accessed.

Creating SQL Hierarchies

ABAP CDS Hierarchies

With CDS hierarchies, you outsource the hierarchy data source and the creation of the SQL hierarchy from your ABAP program to ABAP CDS. There, the hierarchy is a fully fledged CDS entity, it s reusable in different programs or in other CDS entities (views) and can be part of your data model including access control using CDS DCL. For a CDS hierarchy, the hierarchy source cannot be anything else but a CDS view that exposes a hierarchy association. Here is a very simple example for that:

@AccessControl.authorizationCheck: #NOT_REQUIRED
define view entity DEMO_CDS_SIMPLE_TREE_VIEW
as select from demo_simple_tree
association [1..1] to DEMO_CDS_SIMPLE_TREE_VIEW as _tree
on $projection.parent = _tree.id { _tree, key id, parent_id as parent, name }  This CDS view entity accesses the database table DEMO_SIMPLE_TREE , where the actual data reside, and exposes a self-association _tree. The ON-condition of the association defines a parent-child relation between the elements id and parent. It simply means that a row of the result set, where column parent has the same value as column id of another row, is a child of the latter in the hierarchy that is constructed from that view. The CDS view exposes also another column NAME of the database table that represents the remaining data contents. Note, that you can define such CDS views for any available data sources and that the ON-condition can be more complex than shown in the simple example here. Now we can use the above CDS view as the hierarchy source of a CDS hierarchy that can be defined as follows: define hierarchy DEMO_CDS_SIMPLE_TREE with parameters p_id : abap.int4 as parent child hierarchy( source DEMO_CDS_SIMPLE_TREE_SOURCE child to parent association _tree start where id = :p_id siblings order by id ascending ) { id, parent, name }  The CDS DDL statement DEFINE HIERARCHY that can be used in the DDL source code editor of ADT defines a CDS hierarchy as a CDS entity that can be accessed in CDS views or ABAP SQL as a SQL hierarchy. The most important additions of the statement are: • SOURCE for specifying the hierarchy source, here our DEMO_CDS_SIMPLE_TREE_VIEW. • CHILD TO PARENT ASSOCIATION for specifying the hierarchy association, here _tree. • START WHERE for defining the root nodes of the SQL hierarchy, here represented by an input parameter p_id that must be passed when accessing the CDS hierarchy. • SIBLINGS ORDER BY to define also a sort order for sibling nodes besides the sort order that comes from the parent-child relationship anyhow. • An element list { … } that defines the columns of the SQL hierarchy, here simply all elements of the hierarchy source. For a full description and all other additions see DEFINE HIERARCHY. When you access the CDS hierarchy, all lines are selected from the original data source, in our case the database table DEMO_SIMPLE_TREE, that fulfill the START WHERE condition. Those make up the root node set of the SQL hierarchy. In the simplest case we have exactly one root node, but more are possible. Then, for each root node, its descendants are retrieved. That means each line from the database table that fulfills the ON-condition of the hierarchy association is added to the SQL hierarchy. And for each descendant this is done again and again until all descendants are found. And that is basically all! Further additions to DEFINE HIERARCHY allow you to control the creation of the SQL hierarchy, for example, whether multiple parents are allowed or how orphans or cycles should be handled. Besides the elements of the hierarchy, the element list can also contain the hierarchy attributes listed under Hierarchy Attributes. Then the SQL hierarchy is enriched with columns containing information about the role, the current line plays as a hierarchy node, as, for example, the hierarchy rank or the hierarchy level. In our example, we did not add such elements, because ABAP SQL does that implicitly for you when accessing the CDS hierarchy! The SQL hierarchy can be used in an ABAP SQL query by using the CDS hierarchy directly as a data source of the FROM clause: DATA root_id type demo_cds_simple_tree_view-id. ... SELECT FROM demo_cds_simple_tree( p_id = @root_id ) FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(cds_result).  And although we did not define any hierarchy attributes in the element list of the CDS hierarchy, we can add all the hierarchy columns listed under Hierarchy Columns to the SELECT list of our ABAP SQL statement! This is always possible, when a SQL hierarchy is accessed in ABAP SQL. We can pass any ID to the CDS hierarchy now and see what happens. If such a line is found in the database table, the respective hierarchical data will be retrieved and delivered. After filling the database table with randomly generated data we can inspect the tabular result. As expected, the elements of the SELECT list appear as columns. Note, that the contents of column NAME could be anything. It is filled here with a string representation of the path from the root node to the current node for demonstration purposes only. From the ABAP coding point of view, CDS hierarchies are the most convenient way of using SQL hierarchies. Now let us turn to other ways, involving more ABAP, until we do not use CDS any more in the end. ABAP SQL Hierarchy Generator HIERARCHY The ABAP SQL hierarchy generator is a ABAP SQL function named HIERARCHY, that allows you to define a SQL hierarchy in the ABAP program itself. Let us look directly at an example: DATA root_id TYPE demo_cds_simple_tree_view-id. ... SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_view CHILD TO PARENT ASSOCIATION _tree START WHERE id = @root_id SIBLINGS ORDER BY id MULTIPLE PARENTS NOT ALLOWED ) "hierarchy FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(asql_cds_result). ASSERT asql_cds_result = cds_result.  Looks familiar? Well, almost the same syntax used for defining the CDS hierarchy is used in the brackets HIERARCHY( … ) and it does exactly the same! The difference is the same as it is between ABAP SQL joins and joins in CDS views: • If you code it in ABAP SQL, it is for usage in one program only. • If you code it in ABAP CDS, it is for usage in many programs or whole data models. And, as you can see, we dare to prove this with an ASSERT statement. Please also note, that we use the hierarchy columns again. They are implicitly there, when a SQL hierarchy, here created by the hierarchy generator, is accessed. The above hierarchy generator of ABAP SQL accesses the same hierarchy source as the CDS hierarchy, namely the CDS view DEMO_CDS_SIMPLE_TREE_VIEW that exposes the necessary hierarchy association _tree. In the following code snippet, we replace the CDS hierarchy source with a CTE: DATA root_id type demo_cds_simple_tree_view-id. ... WITH +cte_simple_tree_source AS ( SELECT FROM demo_simple_tree FIELDS id, parent_id AS parent, name ) WITH ASSOCIATIONS ( JOIN TO MANY +cte_simple_tree_source AS _tree ON +cte_simple_tree_source~parent = _tree~id ) SELECT FROM HIERARCHY( SOURCE +cte_simple_tree_source CHILD TO PARENT ASSOCIATION _tree START WHERE id = @root_id SIBLINGS ORDER BY id MULTIPLE PARENTS NOT ALLOWED ) "hierarchy FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(asql_cte_result). ASSERT asql_cte_result = cds_result.  Common table expressions (CTEs) are a very powerful tool for defining subqueries that can be used in subsequent queries of the same WITH statement. They can be regarded as an ABAP SQL internal definition of data sources that fulfill the same functionality as program external data sources, especially CDS views. As you see above, the CTE cte_simple_tree_source does the same as the CDS view DEMO_CDS_SIMPLE_TREE_VIEW: • It accesses the database table DEMO_SIMPLE_TREE. • It exposes an association _tree by using the addition WITH ASSOCIATIONS. The main query of the WITH statement uses the hierarchy generator in the same way as the SELECT above, just with the CTE as a data source instead of the CDS view and the result is – of course – the same. For a full description of the hierarchy generator and all other additions see SELECT, FROM HIERARCHY. We managed to create a SQL hierarchy with ABAP SQL means only. Last but not least we will use CTEs as hierarchies themselves. You might skip the following section and turn directly to the hierarchy navigators if you are not too interested in this syntactic gimmicks. ABAP CTE Hierarchies A CTE that produces hierarchical data can declare itself as a SQL hierarchy of a freely defined name with the addition WITH HIERARCHY. That simply means that subsequent queries of the same WITH statement can use the CTE as a hierarchy with its implicit hierarchy columns or – more important – in hierarchy navigators. The following code snippets show the three possibilities, how a CTE can produce hierarchical data: DATA root_id TYPE demo_cds_simple_tree_view-id. ... WITH +tree AS ( SELECT FROM demo_cds_simple_tree( p_id = @root_id ) FIELDS * ) WITH HIERARCHY demo_cds_simple_tree SELECT FROM +tree "hierarchy FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(cte_cds_result). ... WITH +tree AS ( SELECT FROM HIERARCHY( SOURCE demo_cds_simple_tree_view CHILD TO PARENT ASSOCIATION _tree START WHERE id = @root_id SIBLINGS ORDER BY id MULTIPLE PARENTS NOT ALLOWED ) AS asql_hierarchy FIELDS id, parent, name ) WITH HIERARCHY asql_hierarchy SELECT FROM +tree "hierarchy FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(cte_asql_result). ... WITH +cte_simple_tree_source AS ( SELECT FROM demo_simple_tree FIELDS id, parent_id AS parent, name ) WITH ASSOCIATIONS ( JOIN TO MANY +cte_simple_tree_source AS _tree ON +cte_simple_tree_source~parent = _tree~id ), +tree AS ( SELECT FROM HIERARCHY( SOURCE +cte_simple_tree_source CHILD TO PARENT ASSOCIATION _tree START WHERE id = @root_id SIBLINGS ORDER BY id MULTIPLE PARENTS NOT ALLOWED ) AS cte_hierarchy FIELDS id, parent, name ) WITH HIERARCHY cte_hierarchy SELECT FROM +tree "hierarchy FIELDS id, parent, name, hierarchy_rank, hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, hierarchy_is_cycle, hierarchy_is_orphan, node_id, parent_id INTO TABLE @DATA(cte_cte_result). ASSERT cte_cds_result = cds_result. ASSERT cte_asql_result = cds_result. ASSERT cte_cte_result = cds_result.  A CTE that is exposed as a SQL hierarchy must access a SQL hierarchy itself and in the end these are always based on a CDS hierarchy or the ABAP SQL hierarchy generator as shown above. Again, the hierarchy source of the hierarchy generator can be a CDS view or a CTE exposing the hierarchy association. Again, we use assertions to show that all examples give the same results as the CDS hierarchy. Hierarchy Navigators Hierarchy navigators are an additional set of hierarchy functions in ABAP SQL that allow you to work on existing SQL hierarchies instead of creating them. Hierarchy navigators can work on SQL hierarchies created as shown above, namely on CDS hierarchies, the hierarchy generator or a CTE hierarchy. They can be used as data sources in ABAP SQL queries. If you need a SQL hierarchy several times, from a performance point of view it is favorable to create it once with a given set of root nodes and access it with hierarchy navigators then. Furthermore, each hierarchy navigator can add further hierarchy columns to the result set that offer additional possibilities for the evaluation. In the following examples, we access our CDS hierarchy with hierarchy navigators. But you could also replace it with the hierarchy generator or a CTE hierarchy. Check the examples of the documentation, where this is also shown. Hierarchy Node Navigator HIERARCHY_DESCENDANTS As the name says, HIERARCHY_DESCENDANTS fetches all descendants for any nodes from a SQL hierarchy. It adds HIERARCHY_DISTANCE as an additional hierarchy column to the result set. Let us look at an example: DATA root_id TYPE demo_cds_simple_tree_view-id. DATA sub_id TYPE demo_cds_simple_tree_view-id. ... SELECT FROM HIERARCHY_DESCENDANTS( SOURCE demo_cds_simple_tree( p_id = @root_id ) START WHERE id = @sub_id ) FIELDS id, parent_id, name, hierarchy_distance INTO TABLE @DATA(descendants).  Our CDS hierarchy DEMO_CDS_SIMPLE_TREE_VIEW is used to create a SQL hierarchy with a start node passed to parameter p_id and for a node sub_id all descendants are fetched. Running the program for sub_id 264 shows the result including the additional column HIERARCHY_DISTANCE that contains the distance to the respective start node. A further parameter DISTANCE – not shown here – allows you restrict the distance to the respective start node. Hierarchy Node Navigator HIERARCHY_ANCESTORS Now the other way around: ABAP SQL function HIERARCHY_ANCESTORS returns the ancestors of any given node of an existing hierarchy: DATA root_id TYPE demo_cds_simple_tree_view-id. DATA max_id TYPE demo_cds_simple_tree_view-id. ... SELECT FROM HIERARCHY_ANCESTORS( SOURCE demo_cds_simple_tree( p_id = @root_id ) START WHERE id = @max_id ) FIELDS id, parent_id, name, hierarchy_distance INTO TABLE @DATA(ancestors).  Looking at the result for max_id 990, you see that the value of column HIERARCHY_DISTANCE is negative now. Using aggregate functions or evaluating the internal result table, you can now easily extract further information like the number of ancestors and so on. Hierarchy Node Navigator HIERARCHY_SIBLINGS Besides descendants and ancestors, hierarchy nodes also can have siblings, that is nodes that have the same parent node. You can find these by looking for all nodes with the same value in hierarchy column HIERARCHY_PARENT_RANK. But there is also HIERARCHY_SIBLINGS as a hierarchy function for that: DATA root_id TYPE demo_cds_simple_tree_view-id. DATA sibl_id TYPE demo_cds_simple_tree_view-id. ... SELECT FROM HIERARCHY_SIBLINGS( SOURCE demo_cds_simple_tree( p_id = @root_id ) START WHERE id = @sibl_id ) FIELDS id, parent_id, name, hierarchy_sibling_distance INTO TABLE @DATA(siblings).  You see, that this function adds another hierarchy column HIERARCHY_SIBLING_DISTANCE that contains the distance to the respective start node. Running the example for sibl_id 144 gives:. Hierarchy Aggregate Navigators Finally let us turn to the hierarchy aggregate navigators that allow you to apply some aggregate functions to descendants and ancestors of any node of a SQL hierarchy: We will show an example for the descendants case and refer to the documentation for the ancestors. Applying aggregate functions to columns normally means, that you have some data there for which that makes sense. In our simplistic SQL hierarchy tree we do not have such meaningful data. On the other hand, this can also be a use case: You can have the administrative data for the parent-child relation in one database table and the real data in another one. And for that use case, the hierarchy aggregate navigator HIERARCHY_DESCENDANTS_AGGREGATE offers you the possibility to join such data to your SQL hierarchy: TYPES: BEGIN OF value, id TYPE i, amount TYPE p LENGTH 16 DECIMALS 2, END OF value. DATA value_tab TYPE SORTED TABLE OF value WITH UNIQUE KEY id. DATA root_id TYPE demo_cds_simple_tree_view-id. DATA sub_id TYPE demo_cds_simple_tree_view-id. ... SELECT FROM HIERARCHY_DESCENDANTS_AGGREGATE( SOURCE demo_cds_simple_tree( p_id = @sub_id ) AS h JOIN @value_tab AS v ON v~id = h~id MEASURES SUM( v~amount ) AS amount_sum WHERE hierarchy_rank > 1 WITH SUBTOTAL WITH BALANCE ) FIELDS id, amount_sum, hierarchy_rank, hierarchy_aggregate_type INTO TABLE @DATA(descendants_aggregate).  In our example, we join an internal table value_tab of the same program to the SQL hierarchy. In a real life example you would join another database table, of course. On the other hand the example shows ABAP SQL’s capability to use internal tables as data sources. You even can go so far to evaluate hierarchical data in internal tables with ABAP SQL by using an internal table as data source for a CTE hierarchy! The example does the following: • We use the hierarchy aggregate navigator HIERARCHY_DESCENDANTS_AGGREGATE as a data source of a FROM clause. • Our CDS hierarchy DEMO_CDS_SIMPLE_TREE_VIEW joined with internal table value_tab is used as the data source. • The ABAP SQL function returns a tabular result of nodes of the data source. • The aggregate function SUM behind MEASURES sums up the values of column amount of the joined internal table for all descendants of each node returned by the ABAP SQL function. • The WHERE condition restricts the result set by a freely programmable condition. • The WITH additions add further rows to the result set that can be recognized by values in an additional hierarchy column HIERARCHY_AGGREGATE_TYPE: • WITH SUBTOTAL – In the row where HIERARCHY_AGGREGATE_TYPE has value 1, column AMOUNT_SUM contains the sum of the values of all hierarchy nodes that meet the WHERE condition. • WITH BALANCE – In the row where HIERARCHY_AGGREGATE_TYPE has value 2, column AMOUNT_SUM contains the sum of the values of all hierarchy nodes that do not meet the WHERE condition.For more WITH additions see the documentation, Running the example for sub_id 264 gives. In the following, we also show the result of the joined data source, where you can check that the calculated values are correct. Note With ABAP release 7.56/2021, SP01 a program DEMO_SQL_HIERACHIES is shipped, that comprises all of the above code snippets and produces results as shown here. Real Life Example Apart from the abstract examples above, where randomized data are used, let us also show you a code snippet that can be found in currently delivered SAP basis code:  SELECT FROM HIERARCHY_DESCENDANTS( SOURCE HIERARCHY( SOURCE abap_docu_tree_source CHILD TO PARENT ASSOCIATION _tree START WHERE node_key = 'ABENABAP' ) START WHERE node_key = @<to_be_deleted>-object ) FIELDS node_key AS object APPENDING TABLE @DATA(descendants).  The hierarchy navigator HIERARCHY_DESCENDANTS fetches descendants from a SQL hierarchy created by the hierarchy generator based on the following CDS view: define view entity ABAP_DOCU_TREE_SOURCE as select from abapdocu_nodes association [0..*] to ABAP_DOCU_TREE_SOURCE as _tree on$projection.relatkey = _tree.node_key
{
_tree,
key tab_index,
node_key,
relatkey
}

This view projects the fields of a database table ABAPDOCU_NODES and exposes a parent-child hierarchy association between  node_key as parent and relatkey as child. The table (or its predecessor ABAPDOCU_TREE) exists already a long time and stores the nodes of the hierarchical tree structure of the ABAP Keyword Documentation. Before SQL hierarchies were introduced with release 7.53, all tree related logics was handmade. For example, a mesh was used to find subnodes, as follows:

TYPES:
BEGIN OF line,
id        TYPE abapdocu_nodes-node_key,
parent_id TYPE abapdocu_nodes-relatkey,
END OF line,
t_itab TYPE STANDARD TABLE OF line WITH NON-UNIQUE KEY id
WITH NON-UNIQUE SORTED KEY by_parent COMPONENTS parent_id,
BEGIN OF MESH t_mesh ##NEEDED,
node TYPE t_itab
ASSOCIATION to_node TO node ON parent_id = id
USING KEY by_parent,
END OF MESH t_mesh.

DATA mesh TYPE t_mesh.

...

mesh-node = VALUE #( FOR node IN cl_abap_docu_tables_broker=>root->node_table
( id = node-node_key parent_id = node-relatkey ) ).

DATA(subtree) = VALUE t_itab(
FOR <node> IN mesh-node\to_node+[ mesh-node[ id = parent ] ] ( <node> )  ).



Nowadays, this work can happily be passed to ABAP SQL and ABAP CDS and from there to the HANA database.

Releases and DB Support

Availability:

• CDS hierarchies, the ABAP SQL hierarchy generator and most of the hierarchy navigators are available from ABAP 7.53 on.
• Herarchy navigator HIERARCHY_DESCENDANTS_AGGREGATE is available from ABAP 7.54 on.
• Some other features (not covered in this blog) were added in 7.54, 7.55 and 7.56. Check the ABAP CDS and ABAP SQL nodes under News for Release 7.5x.

As a rule, you need a HANA database to work with SQL hierarchies. In order to check whether the current database system or a database system accessed using a secondary connection supports access to hierarchy data, use the method USE_FEATURES of the class CL_ABAP_DBFEATURES and pass the class constant HIERARCHIES to the method.

SELECT, FROM hierarchy_data

Assigned Tags

You must be Logged on to comment or reply to a post.

Welcome back, Horst Keller .

Long time since your last post 🙂

Very Informative. Thanks for the blog Horst Keller .

Can we use the below CDS DEMO_CDS_SIMPLE_TREE in another CDS view ?

For example, I want to use the Hierarchy Navigator functions and use the result to join with other data source to build a Fiori or Analytical report via CDS.

1. If yes, Can you please give some light on it.
2. Can we build this CDS without a start point ( without paramter p_id and START WHERE )
define hierarchy DEMO_CDS_SIMPLE_TREE
with parameters
p_id : abap.int4
as parent child hierarchy(
source
DEMO_CDS_SIMPLE_TREE_SOURCE
child to parent association _tree
start where
id = :p_id
siblings order by
id ascending
)
{
id,
parent,
name
} 
Horst Keller
Blog Post Author

A CDS hierarchy is a CDS entity that can be used as other CDS entities. Especially it can be used as a data source in another CDS view. There are some restrictions in RAP. You cannot expose a CDS hierarchy as a Business Service directly. But you can wrap it in a view,

You can build CDS hierarchies without input parameters and even without START WHERE. But the latter is not recommended.

Hierarchy navigators cannot be used in ABAP CDS, but in ABAP SQL only.

Hi Horst Keller ,

thank you for this very helpful blog.

What would you suggest as an alternative to using Hierarchy Navigator HIERARCHY_ANCESTORS in a CDS view?

What I like to achieve is to have an association to all ancestors in a CDS view and utilizing the hierarchy cache for this.

Could I do this in AMDP with a table function and using Hierarchy Navigator HIERARCHY_ANCESTORS on HANA level? Can I access a ABAP CDS Hierarchy from HANA SQLScript (I assume no, since there no DDIC view for ABAP CDS Hierarchy)? Can I define an ABAP CDS view on the hierarchy first and use the DDIC view from this view in AMDP?

Wolfgang

Horst Keller
Blog Post Author

Hello Wolfgang,

AMDP's Helmut Prestel helped me to answer the question: "Yes you can wildly mix everything in AMDP".

And Harish Mehta from ABAP CDS even provided an example:

class zhm_cl_amdp_dcv_test implementation.
method is_dcv_exists
by database function for hdb
language sqlscript
using zemh_hierarchytest1.
RETURN
SELECT
employee              as a,
employeename          AS c,
manager               as l,
node_id               as u,
hierarchy_rank        as d,
hierarchy_parent_rank as z_rename
FROM
HIERARCHY_DESCENDANTS(
SOURCE zemh_hierarchytest1
START WHERE employee = '00000002'
)
ORDER BY hierarchy_rank;


Kind regards

Horst

Hi Horst Keller ,

thank you for getting your colleagues involved and thank you Harish Mehta for the helpful example.

One suggestion to you, Horst: in the relevant ABAP Help page titled "METHOD, BY DATABASE PROCEDURE, FUNCTION", there is "The following must be specified after USING: All CDS views, CDS table functions, and CDS hierarchies using the name of the CDS entity"; so far so good.

But some lines later with "For CDS views and CDS table functions, these are the names of the associated SQL views or AMDP functions." I got doubts leading to my question, because a CDS hierarchy does not have an associated SQL View (also a view entity or a projection view would have no associated SQL View). Could you add a line that for these the CDS name can be used directly? Also it would helpful to already mention in this context that if these names are ABAP namespaced, you need to use the name in upper case with quotation marks around in the SQLscript.

Again thank you very much and keep going with your excellent work for the ABAP Doc,

Wolfgang

Horst Keller
Blog Post Author

OK, I noted that.

Nice blog, Horst. You might want to amend your "All you have to do..." with "upgrade your system to at least 7.53" and some features you mention require 7.54. 😉

CL_ABAP_DBFEATURES method USE_FEATURES ( "HIERARCHIES") might come in handy for developers having to support scenarios across different releases.

Horst Keller
Blog Post Author

Hi Marc,

Thanks for the hint.I added a small section "Releases and DB support" at the end.

Hello Horst,

Thank you for this comprehensive article.

I have a specific case where I would like to use this feature but i'm blocking.
This involves retrieving the sites associated with a contract and then retrieving the descendant hierarchies of these sites.
I would like to do everything in a CDS.

The problem is that I don't know how to specify multiple root nodes from a request.and You say: "In the simplest case we have exactly one root node, but more are possible."

In the documentation we have:
... START WHERE cds_cond
The operands on the right side can be literals, parameters from the parameter list of the hierarchy, and session variables.

With a start where I will not be able to achieve what I want but they also say

If the addition START WHERE is not specified explicitly, it is added implicitly with a condition that checks the parent node defined in the hierarchy association in question for its initial value.

And there it is not very clear for me !
Do you have any clues? Thank you

Horst Keller
Blog Post Author

Hello,

The START WHERE condition selects the root node set from which the child nodes are determined.

If there is no explicit START WHERE condition in a CDS hierarchy, the root node set is impllictly made up from all rows where the parent column is initial,

In our simple example, the following CDS hierarchy without START WHERE condition:

define hierarchy ...
source
DEMO_CDS_SIMPLE_TREE_SOURCE
child to parent association _tree
siblings order by
id ascending
)
{
id,
parent,
name
}

is the same as:

define hierarchy ...
as parent child hierarchy(
source
DEMO_CDS_SIMPLE_TREE_SOURCE
child to parent association _tree
start where parent is initial
siblings order by
id ascending
)
{
id,
parent,
name
}

With other words, an implicit START WHERE condition is added that depends on the parent column, which again depends on the hierarchy association.

Hi Horst Keller , is it possible to use a sub-query to specify a list of root nodes? I know that the syntax doesn't allow that, but can something like the below be implemented using hierarchies?

start where id in ( select id from some_table where some_field = some_value )

Probably, it's possible to achieve it with the WITH statement, but i never used them in my life... And i can't create a CDS view in this case, so i have to use ABAP SQL only. Real example can be the IFLOT table where i need to select functional locations matching a certain condition (e.g., a specific FL category) and all their descendants. Is this possible?

UPDATE: I think i can implement the filtering criteria in the SOURCE table, but i still can't build a working example... That is, i managed to get this show some results:

with
+fl_tree_src as
( select from iflot
fields tplnr, fltyp, tplma
where fltyp = 'H' )
with associations (
join to many +fl_tree_src as _tree
on +fl_tree_src~tplma = _tree~tplnr ),
+fl_tree as
( select from hierarchy(
source +fl_tree_src
child to parent association _tree
start where tplma is initial
siblings order by tplnr
multiple parents allowed
orphans root ) as hier
fields tplnr, fltyp, tplma )
with hierarchy hier
select from +fl_tree
fields tplnr,
tplma,
fltyp,
hierarchy_tree_size,
hierarchy_level
into table @data(fl_hier_result).

But i don't know how to select only DESCENDANTS of the SOURCE, that is, only descendants of the Functional Locations that match the query

select from iflot
fields tplnr, fltyp, tplma
where fltyp = 'H'

Well, the way it's working, as it looks, i get ONLY locations of category 'H'. However, i need to select functional locations of category 'H' only as a starting set of nodes, not the filter that must be applied to child nodes. This is where i'm getting stuck.