Flattening the hierarchy using SQL code

Currently HANA doesn’t provide the recursive traversing of Hierarchy like other database which provide this feature using CONNECT BY clause or by using CTE (Common Table Expression). The information on the Hierarchical Query and CTE can be found here.

The recursive feature is very much required in multiple use cases like the Employee Manager relationship, defining custom authorization based on Hierarchy based information and so on.

The following code helps flattening the hierarchy and inserts the data into another table which can be subsequently used for the appropriate application.

The sample base table can contain data like:

The hierarchy data can be traversed recursively and flattened to provide the output like:

The SQL Code with the sample data can be found below:

— Base table and data —

CREATE COLUMN TABLE T_HIER (NODE smallint, NODENAME varchar(2), PARENTNODE smallint);

insert into T_HIER values (1, ”, null);

insert into T_HIER values (2, ‘A’, 1);

insert into T_HIER values (3, ‘B’, 1);

insert into T_HIER values (4, ‘C’, 2);

insert into T_HIER values (5, ‘D’, 2);

insert into T_HIER values (6, ‘E’, 3);

insert into T_HIER values (7, ‘F’, 4);

— check the table contents —

select * from t_hier;

— Ignore the drop table error first time —

DROP TABLE TC_R_FLAT_HIER;

CREATE COLUMN TABLE TC_R_FLAT_HIER (NODE smallint, NODENAME varchar(2), PARENTNODE smallint, PARENTNODENAME varchar(2), CSTATUS varchar(1));

— Ignore the drop procedure error first time —

DROP PROCEDURE PR_R_FLAT_HIER_01;

CREATE PROCEDURE PR_R_FLAT_HIER_01 ()

LANGUAGE SQLSCRIPT AS

   vl_cnt       integer := 1;

BEGIN

— Empty the table for the new search

   delete from TC_R_FLAT_HIER;

— Insert data for the first level objects

   insert into TC_R_FLAT_HIER (NODE, NODENAME, PARENTNODE, PARENTNODENAME, CSTATUS)

   select C.NODE, C.NODENAME, C.PARENTNODE, P.NODENAME, ‘n’

   from T_HIER C left outer join T_HIER P on C.PARENTNODE = P.NODE;

— Check if there are any objects to search 

   select count(*) into vl_cnt from TC_R_FLAT_HIER where CSTATUS = ‘n’;

   WHILE :vl_cnt > 0 DO

—    Set the objects to Search from New

      update TC_R_FLAT_HIER set CSTATUS = ‘s’ where CSTATUS = ‘n’;

      insert into TC_R_FLAT_HIER (NODE, NODENAME, PARENTNODE, PARENTNODENAME, CSTATUS)

      select distinct B.PARENTNODE, B.NODENAME, C.PARENTNODE, P.NODENAME, ‘n’

      from TC_R_FLAT_HIER B left outer join T_HIER C on B.PARENTNODE = C.NODE

       left outer join T_HIER P on C.PARENTNODE = P.NODE

      where B.CSTATUS = ‘s’

      and B.PARENTNODE is not null;

—    Set the objects to Search Completed from Search

      update TC_R_FLAT_HIER set CSTATUS = ‘c’ where CSTATUS = ‘s’;

—    Check if there are any objects to search 

      select count(*) into vl_cnt from TC_R_FLAT_HIER where CSTATUS = ‘n’;

   END WHILE;

END;

— check the flattended hierarchy table contents —

select * from TC_R_FLAT_HIER order by 1;

Please feel free to comment / suggest improvements to the code. Hope this helps in some of your use cases.