SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




CRM Companies List
Web Based CRM Software


Query Hierarchy Data in SAP HANA Database using SQLScript


With SAP HANA 2 SP02, SQLScript enhancements on HANA database enables SQL developers to query hierarchy data stored as parent-child relation in database tables easily using SQLScript Hierarchy function. In this SQLScript tutorial, I want to show SQL programmers how they can query and filter hierarchical data with sample cases using SQL Hierarchy function.

SQL Server provided a similar hierarchy function and enabled the use of recursive CTE queries for hierarchy data for long time ago.
It is very good that SQLScript developers have the same option and easiness for querying hierarchical data on SAP HANA database tables.


Hierarchy Data as Parent-Child Rows in SAP HANA Database

Let's create our sample hierarchy table in our database and populate it with parent-child records simulating a company organization.

Create Column Table Organization (
 Id int generated by default as identity(start with 1 increment by 1) not null,
 Unit varchar(100) Not Null,
 ParentID int
);
Insert Into Organization (Unit, ParentId) values ('CEO', NULL);
Insert Into Organization (Unit, ParentId) values ('Sales Department', 1);
Insert Into Organization (Unit, ParentId) values ('Administration Department', 1);
Insert Into Organization (Unit, ParentId) values ('Customer Service Department', 1);
Insert Into Organization (Unit, ParentId) values ('Marketing Department', 1);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 2);
Insert Into Organization (Unit, ParentId) values ('Research Department', 2);
Insert Into Organization (Unit, ParentId) values ('Engineering Department', 2);
Insert Into Organization (Unit, ParentId) values ('Human Resources Department', 3);
Insert Into Organization (Unit, ParentId) values ('Operation Department', 3);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 4);
Insert Into Organization (Unit, ParentId) values ('Research Department', 4);
Insert Into Organization (Unit, ParentId) values ('Products Division A', 5);
Insert Into Organization (Unit, ParentId) values ('Products Division B', 5);
Insert Into Organization (Unit, ParentId) values ('Training Group', 9);
Insert Into Organization (Unit, ParentId) values ('Recruiting Team', 9);
Insert Into Organization (Unit, ParentId) values ('Salary Group', 9);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 10);
Insert Into Organization (Unit, ParentId) values ('Research Department', 10);
Insert Into Organization (Unit, ParentId) values ('Overseas Business Department', 13);
Insert Into Organization (Unit, ParentId) values ('Domestic Business Department', 13);
Insert Into Organization (Unit, ParentId) values ('Direct Sales Department', 14);
Insert Into Organization (Unit, ParentId) values ('Channel Department', 14);

The hierarchical data that I wanted to create in SAP HANA database using above SQL DML commands on parent-child table Organization is following organizational structure of a fictional company.

sample organizational hierarchy data for SAP HANA SQLScript tutorial

I used identity value for the ID column, SQL developers can refer to SQLScript Identity Column for details.


Query Hierarchy Data using SQLScript Hierarchy Function on HANA Database

On a parent - child table, SQL programmers can easily use SQLScript function Hierarchy to resolve the hierarchical ranks, relations, levels of a node easily.

Here is the simplest syntax for calling Hierarchy function using SQLScript on SAP HANA database
It is must to use HIERARCHY function name in the FROM clause just like a table (as it is a table function) and define the hierarchy in a predefined format in the SOURCE() of the function argument.

The source query, should have a node_id and a parent_id column to identify the relation in the hierarchical data structure.

select *
from HIERARCHY (
 source (
  select
   id as node_id,
   parentid as parent_id,
   unit
  from Organization
 )
)

And the output is as follows on given parent-child sample hierarchical data

query parent-child hierarchical data using SQLScript on SAP HANA database

As I marked on the above output screenshot, the most important columns of the Hierarchy function output table is; Hierarchy_Level and Hierarchy_Parent_Rank

Hierarchy_Level is showing the position of levels from top to down of the current node in the hierarchical structure.
I tried to show the levels or hierarchy ranks of nodes in the organizational structure to make it visually visible in the following diagram.

HANA database SQLScript hierarch levels in parent-child hierarchical data


List Parent Nodes or Ancestors of a Hierarchy Node on SAP HANA Database

So if you are searching for the upper-level managers or upper organizational units in an organizational structure, which SQL query HANA database developers should use?

SQLScript enhancements introduced SAP HANA database programmers the hierarchy function HIERARCHY_ANCESTORS to query nodes in a hierarchical structure for parent nodes

Here is a sample SQL query which lists all the parents of a node in given hierarchy data in this tutorial.

select *
from HIERARCHY_ANCESTORS (
 SOURCE HIERARCHY (
  source (
   select
    id as node_id,
    parentid as parent_id,
    unit
   from Organization
  )
 )
 START WHERE node_id = 23
)

As SQL developers can see easily, I have listed all parents of organizational unit "Channel Department" which has Id as 23 in the database table.

query hierarchy data to list all parents with HANA database Hierarchy function

Please note that the hierarchy_distance column shows the levels between two nodes. For parent nodes, the distance is expressed in negative values.

Let's combine our knowledge and list only the parent node names (unit descriptions) in order starting from top to bottom seperated by ">" character.

select
 string_agg(unit, ' > ' order by hierarchy_rank) as bottom2up
from HIERARCHY_ANCESTORS (
 SOURCE HIERARCHY (
  source (
   select
    id as node_id,
    parentid as parent_id,
    unit
   from Organization
  )
 )
 START WHERE node_id = 23
)

Here is the output: "CEO > Marketing Department > Products Division B > Channel Department"

list of parent nodes in hierarchy in HANA database using SQL


Display Child Nodes of a Given Hierarchy Node using SQL

Let's this time, display the child nodes of a given node in the hierarchy using SQLScript hierarchy functions on our sample HANA database table.
Assume business requirement needs to see the child nodes of "Marketing Department" which has the node ID value as 5

This time we will use SQL HIERARCHY_DESCENDANTS functions.
I want to create a string output variable with indents according to the node level compared to top most parent node "Marketing Department"

Simple SQL query is as follows

select *
from HIERARCHY_DESCENDANTS (
 SOURCE HIERARCHY (
  source (
   select
    id as node_id,
    parentid as parent_id,
    unit
   from Organization
  )
 )
 START WHERE node_id = 5
)

But for inserting indents showing visually the hierarchy between node, SQLScript programmers can modify the above SELECT statement as follows:

select
 string_agg(
  LPAD('', HIERARCHY_DISTANCE, CHAR(9)) || unit || BINTOSTR(HEXTOBIN('0D0A')),
  '' order by hierarchy_rank
 ) as topdown
from HIERARCHY_DESCENDANTS (
 SOURCE HIERARCHY (
  source (
   select
    id as node_id,
    parentid as parent_id,
    unit
   from Organization
  )
 )
 START WHERE node_id = 5
)

I added TAB character which can be managed by using CHAR(9)
The carriage return character can be managed by using BINTOSTR(HEXTOBIN('0D0A'))

The output of the above SQLScript code execution on tutorial sample hierarchy data will be as follows

display hierarchy data with tabs visually using HANA database SQLScript query


Display Sibling Nodes on HANA Hierarchy Table using SQLScript

SQL programmers can use another Hierarchy function named HIERARCHY_SIBLINGS function to query a given hierarchical structure for the nodes sharing the same level or for siblings of a given parent-child node.

For example, if we want to get the same level nodes with "Marketing Department" as marked in below diagram, HANA database developers can use HIERARCHY_SIBLINGS function and get the desired results easily.

query for siblings on SAP HANA database hierarchy data

The SQLScript query that will be used by HANA database programmer is as follows

select
 node_id, parent_id, unit
from HIERARCHY_SIBLINGS (
 SOURCE HIERARCHY (
  source (
   select
    id as node_id,
    parentid as parent_id,
    unit
   from Organization
  )
 )
 START WHERE node_id = 5
)

And the output will be just as we wanted to have:

SQLScript query hierarchy data for siblings on HANA database

For more on Hierarchy function SQLScript database developers can refer to official documentation

In the reference documentation, SQL programmers developing on SAP HANA database will see two more hierarchy functions namely HIERARCHY_LEVELED function and HIERARCHY_COMPOSITE_ID function.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems