SAP® HANA Analysis Tool Hierarchy is a special form of hierarchy implementation that uses mathematical sets to provide results fast and with fewer queries, comparing to standard hierarchy recursive implementation.

There is no recursive calling necessary when searching in the hierarchy table. However, there is a need for recalculation when adding values.

It is the implementation of Nested set model

Advantages

• Get the important queries in one select only. No recursive calls are necessary.

Disadvantage

• Some node IDs have to be recalculated when adding or deleting a node, therefore they are not the same over the whole life of the hierarchy.
  However, a node name or a new column with the whole life unique identification can be used/added.

If you need to only use the procedures and don’t want to read the whole concept, just jump to the list of all the procedures and copy/paste the needed code.

Part of the solution is also visualization, which will show the hierarchy in the text format:

ROOT
  +- Field name
         +- Field name (maybe changed)
         |      +- a formula for the field (for example)
         +- Field name
                +- TABLE_FIELD
...

Concept

The principal of the solution is to change a hierarchy into a mathematical set, where all the nodes have borders numbered from left to right increasingly.

For example the following hierarchy:

Can be presented as a mathematical set in the following way:

Where the mathematical set can be then transformed into the table:

The big advantage is, that most of the standard search queries can be done in only one single command.

For example:

• Get all children of the node X (also children of the children)

  SELECT * FROM hierarchy
  WHERE Left > "node's X Left"
  AND Right < "node's X Right";

• Get parent of the node X

  SELECT * FROM hierarchy 
  WHERE Left < "node's X Left"
  AND Right > "node's X Right"
  AND Level = ("node's X Level" - 1);

• Get next node of the node X

  SELECT * FROM hierarchy 
  WHERE Left = "node's X Right"
  AND Level = "node's X Level";

• No. of children of the node X

  SELECT (((Right – Left) – 1) / 2) AS "No. of children" FROM hierarchy 
  WHERE Left = "node's X Left";

Implementation in SAP® HANA Procedures (v.200508)

The implementation has been done by using HANA procedures in HANA 1.0 SPS 12 version, which means it will work also in the HANA 2.x and most likely also in any future versions.

The ARRAY functionality has been used to significantly increase performance. Also, all the operations are done in memory by sharing data between the procedures unless is the hierarchy saved.

The main implementation object is Hierarchy table, consist of the following columns:

drop table "PMYNARIK".HAT_HIERARCHY;
create column table "PMYNARIK".HAT_HIERARCHY(
"HIERARCHY_ID" VARCHAR(5000) not null, 
"LEFT" INTEGER not null,
"RIGHT" INTEGER not null,
"LEVEL" INTEGER not null,
"NODE_NAME" VARCHAR(5000),
"NODE_CONTENT" nclob,
"NODE_DETAIL" INTEGER not null,
PRIMARY KEY ("HIERARCHY_ID", "LEFT")
);

As a default, the Hierarchy table should consist of the top node: ROOT, with the following default values (default output after the hierarchy initialization):

The HANA Procedures

The logic is implemented in the following procedures, where kind of the OOP approach has been followed.

How-To implement the procedures into the system

Simply create procedures in the proper order as presented below in the following subchapters.

How-To use the procedures

To start using the solution, run the initialization of a hierarchy first, please:

CREATE PROCEDURE...

declare lt_hierarchy table ( 
  "LEFT" INTEGER, 
  "RIGHT" INTEGER, 
  "LEVEL" INTEGER, 
  "NODE_NAME" VARCHAR(255),
  "NODE_CONTENT" nclob, 
  "NODE_DETAIL" INTEGER);

call "PMYNARIK".HAT_AR_HIERARCHY_INIT(lt_hierarchy);

...
END;

To add a new node(s), please use the following example:

CREATE PROCEDURE...

declare lv_node_id integer;
declare lt_hierarchy table (
  "LEFT" INTEGER, 
  "RIGHT" INTEGER, 
  "LEVEL" INTEGER, 
  "NODE_NAME" VARCHAR(255),
  "NODE_CONTENT" nclob, 
  "NODE_DETAIL" INTEGER);

declare lt_visualization table (
	"VISUALIZATION" varchar(1000),
	"LEFT" INTEGER,
	"RIGHT" INTEGER,
	"LEVEL" INTEGER,
	"NODE_NAME" VARCHAR(255),
        "NODE_CONTENT" nclob,
	"NODE_DETAIL" INTEGER);


-- Initialization
call "PMYNARIK".HAT_AR_HIERARCHY_INIT(lt_hierarchy);


-- Add node. Parent node in ROOT, therefore the ID = 1. Node details are not set, therefore NODE_DETAIL = 0
call "PMYNARIK".HAT_AR_HIERARCHY_ADD_NODE(:lt_hierarchy, 1, 'Node A', 0, lt_hierarchy, lv_node_id);


-- Add child node to node A. Node details are not set, therefore NODE_DETAIL = 0
call "PMYNARIK".HAT_AR_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'Node B', 0, lt_hierarchy, lv_node_id);


-- Visualize the hierarchy
call "PMYNARIK".HAT_AR_HIERARCHY_VISUALIZE(:lt_hierarchy, lt_visualization);
select * from :lt_visualization where "LEFT" is not null order by "LEFT";

...
END;

More examples, how to use the procedures, can be found in the optional procedure: HAT_AR_HIERARCHY_TEST.

The List of all the procedures

01 HAT_HIERARCHY Tables

In case you would like to save the created hierarchy, you can use the following table. This is not mandatory.

drop table "PMYNARIK".HAT_HIERARCHY;
create column table "PMYNARIK".HAT_HIERARCHY(
"HIERARCHY_ID" VARCHAR(5000) not null, 
"LEFT" INTEGER not null,
"RIGHT" INTEGER not null,
"LEVEL" INTEGER not null,
"NODE_NAME" VARCHAR(5000),
"NODE_CONTENT" nclob,
"NODE_DETAIL" INTEGER not null,
PRIMARY KEY ("HIERARCHY_ID", "LEFT")
);

02 HAT_HIERARCHY_INIT

Initialize the ROOT node for the hierarchy. This is the mandatory function!

DROP procedure "PMYNARIK".HAT_HIERARCHY_INIT;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_INIT
(
  out out_hierarchy table (
    "LEFT" INTEGER,
    "RIGHT" INTEGER,
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER
    )
) language sqlscript
sql security invoker as
BEGIN

  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(255) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  
  -- Set default
  la_left[1] = 1;
  la_right[1] = 2;
  la_level[1] = 1;
  la_node_name[1] = 'ROOT';
  la_node_content[1] = '';
  la_node_detail[1] = 0;
  
  
  -- Output
  out_hierarchy = UNNEST(:la_left, :la_right, :la_level, :la_node_name, :la_node_content, :la_node_detail) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

03 HAT_HIERARCHY_GET_NODE

Get node based on the node ID (the Left column in the hierarchy table). To get node based on its node name, please use the procedure: Get Nodes. The main difference is, that node ID is unique but the node name is not.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_NODE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_NODE
(
  in in_hierarchy table ( 
    "LEFT" INTEGER,
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  in in_node_id INTEGER,
  in in_check_existence TINYINT default 1,
  out out_node table ( 
    "LEFT" INTEGER,
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare lv_left integer default 0;


  -- Check if node exists
  if in_check_existence = 1 then
    select "LEFT" into lv_left
      from :in_hierarchy
      where "LEFT" = :in_node_id;
  end if;
  

  -- Get node
  out_node = select * from :in_hierarchy
    where "LEFT" = :in_node_id;
END;

04 HAT_HIERARCHY_ADD_NODE

Add a new node.

DROP procedure "PMYNARIK".HAT_HIERARCHY_ADD_NODE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_ADD_NODE
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  in in_parent_node_id INTEGER,
  in in_node_name VARCHAR(5000),
  in in_node_content nclob,
  in in_node_detail_id INTEGER default 0,
  out out_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(255),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  out out_node_id integer
) language sqlscript
sql security invoker as
BEGIN

  declare lv_parent_node_id integer;
  declare lv_parent_left integer;
  declare lv_parent_right integer;
  declare lv_parent_level integer;
  
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare la_left_tmp integer array;
  declare la_right_tmp integer array;
  declare la_level_tmp integer array;
  declare la_node_name_tmp VARCHAR(5000) array;
  declare la_node_content_tmp nclob array;
  declare la_node_detail_tmp integer array;
  
  declare lv_int integer;
  declare lv_left integer;
  declare lv_card integer;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(255), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  
  
  -- Default
  lv_parent_node_id = :in_parent_node_id;	
  lt_hierarchy = select * from :in_hierarchy;-- <= Important, do not change!
  /* 
    The lt_hierarchy table must have all the indexes also for the null values
    Otherwise, the recalculation has to be implemented!
  */
  
  
  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!


  -- Check
  lv_card = CARDINALITY(:la_left);
  if :lv_parent_node_id > lv_card then
    out_node_id = -1;
    return;
  end if;
  
  
  -- Get parent
  lv_parent_left = :lv_parent_node_id;
  lv_parent_right = :la_right[:lv_parent_left];
  lv_parent_level = :la_level[:lv_parent_left];
  
  
  -- Change the current values
  lv_card = CARDINALITY(:la_left);
  for lv_int in 1 .. :lv_card do
    if :la_left[:lv_int] is null then
      continue;
    end if;
    
    if :lv_int > :lv_parent_right then
      la_left_tmp[:lv_int + 2] = :lv_int + 2;
      la_right_tmp[:lv_int + 2] = :la_right[:lv_int];
      la_level_tmp[:lv_int + 2] = :la_level[:lv_int];
      la_node_name_tmp[:lv_int + 2] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_int + 2] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_int + 2] = :la_node_detail[:lv_int];
      
      if :la_right[:lv_int] >= :lv_parent_right then 
        la_right_tmp[:lv_int + 2] = :la_right[:lv_int] + 2;	
      end if;
      
    else
      la_left_tmp[:lv_int] = :la_left[:lv_int];
      la_right_tmp[:lv_int] = :la_right[:lv_int];
      la_level_tmp[:lv_int] = :la_level[:lv_int];
      la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
      
      if :la_right[:lv_int] >= :lv_parent_right then 
        la_right_tmp[:lv_int] = :la_right[:lv_int] + 2;	
      end if;
    end if;
  end for;
  
  
  -- Add the new node
  la_left_tmp[:lv_parent_right] = :lv_parent_right;
  la_right_tmp[:lv_parent_right] = :lv_parent_right + 1;
  la_level_tmp[:lv_parent_right] = :lv_parent_level + 1;
  la_node_name_tmp[:lv_parent_right] = :in_node_name;
  la_node_content_tmp[:lv_parent_right] = :in_node_content;
  la_node_detail_tmp[:lv_parent_right] = :in_node_detail_id;
  

  -- Output
  out_hierarchy = UNNEST(:la_left_tmp, :la_right_tmp, :la_level_tmp, :la_node_name_tmp, :la_node_content_tmp, :la_node_detail_tmp) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
  out_node_id = :lv_parent_right;
END;

05 HAT_HIERARCHY_GET_CHILDREN

Get direct children of the node (on the below level). Or all the children, which means also children of the children in all below levels of the selected node.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_CHILDREN;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_CHILDREN
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  in in_parent_node_id INTEGER,
  in in_incl_children_of_children TINYINT default 0,
  out out_children table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare lv_parent_left integer;
  declare lv_parent_right integer;
  declare lv_parent_level integer;
  
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare la_left_tmp integer array;
  declare la_right_tmp integer array;
  declare la_level_tmp integer array;
  declare la_node_name_tmp VARCHAR(5000) array;
  declare la_node_content_tmp nclob array;
  declare la_node_detail_tmp integer array;
  
  declare lv_parent_node_id integer;
  declare lv_int integer;
  --declare lv_left integer;
  declare lv_card integer;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  
  
  -- Default
  lt_hierarchy = select * from :in_hierarchy;
  lv_parent_node_id = :in_parent_node_id;
  

  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!
  

  -- Get parent node
  lv_parent_left = :lv_parent_node_id;
  lv_parent_right = :la_right[:lv_parent_left];
  lv_parent_level = :la_level[:lv_parent_left];
  

  -- Get Children 
  lv_card = CARDINALITY(:la_left);
  if in_incl_children_of_children = 0 Then
    for lv_int in :lv_parent_left .. :lv_card do
      if :la_left[:lv_int] is null then
        continue;
      end if;
    
      if :lv_int > :lv_parent_left
        and :la_right[:lv_int] < :lv_parent_right
        and :la_level[:lv_int] = (:lv_parent_level + 1)
        then
        
        la_left_tmp[:lv_int] = :lv_int;
        la_right_tmp[:lv_int] = :la_right[:lv_int];
        la_level_tmp[:lv_int] = :la_level[:lv_int];
        la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
        la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
        la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
      end if;
    end for;
    
  else
    for lv_int in :lv_parent_left .. :lv_card do
      if :la_left[:lv_int] is null then
        continue;
      end if;
    
      if :lv_int > :lv_parent_left
        and :la_right[:lv_int] < :lv_parent_right
        then
        
        la_left_tmp[:lv_int] = :lv_int;
        la_right_tmp[:lv_int] = :la_right[:lv_int];
        la_level_tmp[:lv_int] = :la_level[:lv_int];
        la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
        la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
        la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
      end if;
    end for;
  end if;
  
  
  -- Output
  out_children = UNNEST(:la_left_tmp, :la_right_tmp, :la_level_tmp, :la_node_name_tmp, :la_node_content, :la_node_detail_tmp) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

06 HAT_HIERARCHY_GET_NODES

Get node(s) based on the node name. Because the node name is not unique, more than one node can be returned.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_NODES;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_NODES
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  in in_node_name VARCHAR(5000),
  in in_check_existence TINYINT default 1,
  out out_nodes table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare lv_node_name VARCHAR(255);


  -- Check if node exists
  if in_check_existence = 1 then
    select "NODE_NAME" into lv_node_name
      from :in_hierarchy
      where "NODE_NAME" = :in_node_name;
  end if;


  -- Get node
  out_nodes = select * from :in_hierarchy
    where "NODE_NAME" = :in_node_name;
END;

07 HAT_HIERARCHY_GET_PARENTS

Get the direct parent of the node. Or all the parents, which means also parents of the parents in all the above levels of the selected node.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_PARENTS;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_PARENTS
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  in in_child_node_id INTEGER,
  in in_incl_parents_of_parents TINYINT default 0, -- return also parents of the parent
  out out_parents table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare lv_child_left integer;
  declare lv_child_right integer;
  declare lv_child_level integer;
  
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare la_left_tmp integer array;
  declare la_right_tmp integer array;
  declare la_level_tmp integer array;
  declare la_node_name_tmp VARCHAR(5000) array;
  declare la_node_content_tmp nclob array;
  declare la_node_detail_tmp integer array;
  
  declare lv_child_node_id integer;
  declare lv_int integer;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  
  
  -- Default
  lt_hierarchy = select * from :in_hierarchy;
  lv_child_node_id = :in_child_node_id;
  

  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!


  -- Get child node
  lv_child_left = :lv_child_node_id;
  lv_child_right = :la_right[:lv_child_left];
  lv_child_level = :la_level[:lv_child_left];
  

  -- Get Parents 
  if :in_incl_parents_of_parents = 0 Then
    for lv_int in 1 .. (:lv_child_left - 1) do
      if :la_left[:lv_int] is null then
        continue;
      end if;
      
      if :lv_int < :lv_child_left
        and :la_right[:lv_int] > :lv_child_right
        and :la_level[:lv_int] = (:lv_child_level - 1)
        then
        
        la_left_tmp[:lv_int] = :lv_int;
        la_right_tmp[:lv_int] = :la_right[:lv_int];
        la_level_tmp[:lv_int] = :la_level[:lv_int];
        la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
        la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
        la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
      end if;
    end for;
    
  else
    for lv_int in 1 .. (:lv_child_left - 1) do
      if :la_left[:lv_int] is null then
        continue;
      end if;
    
      if :lv_int < :lv_child_left
        and :la_right[:lv_int] > :lv_child_right
        then
        
        la_left_tmp[:lv_int] = :lv_int;
        la_right_tmp[:lv_int] = :la_right[:lv_int];
        la_level_tmp[:lv_int] = :la_level[:lv_int];
        la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
        la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
        la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
      end if;
    end for;
  end if;
  
  
  -- Output
  out_parents = UNNEST(:la_left_tmp, :la_right_tmp, :la_level_tmp, :la_node_name_tmp, :la_node_content_tmp, :la_node_detail_tmp) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

08 HAT_HIERARCHY_DELETE_NODE

Delete selected node and all its children including children of the children.

After the deletion the recalculation is necessary. In principle, the recalculation is an easy mathematical formula:

• All borders of the nodes on the right side of the deleted node(s) are decreasing by: number of deleted nodes * 2

DROP procedure "PMYNARIK".HAT_HIERARCHY_DELETE_NODE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_DELETE_NODE
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  in in_node_id INTEGER,
  out out_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN
/*
  Will delete node and all its children!
*/
  
  declare lv_node_left integer;
  declare lv_node_right integer;
  declare lv_delta integer;
  
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare la_left_tmp integer array;
  declare la_right_tmp integer array;
  declare la_level_tmp integer array;
  declare la_node_name_tmp VARCHAR(5000) array;
  declare la_node_content_tmp nclob array;
  declare la_node_detail_tmp integer array;
  
  declare lv_int integer;
  declare lv_left integer;
  declare lv_card integer;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  
  
  -- Default
  lt_hierarchy = select * from :in_hierarchy;-- <= Important, do not change!
  
  
  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!


  -- Get node
  lv_node_left = :in_node_id;	
  lv_node_right = :la_right[:lv_node_left];
  
  
  -- Get all nodes to be deleted => delta
  lv_delta = :lv_node_right - :lv_node_left + 1;
  
  
  -- Delete the nodes
  lv_card = CARDINALITY(:la_left);
  for lv_int in 1 .. :lv_card do
    if :la_left[:lv_int] is null then
      continue;
    end if;
  
    -- Left > node_right => Left = Left - delta, Right = Right - delta
    if :lv_int > :lv_node_right then
      la_left_tmp[:lv_int - :lv_delta] = :lv_int - :lv_delta;
      la_right_tmp[:lv_int - :lv_delta] = :la_right[:lv_int] - :lv_delta;
      la_level_tmp[:lv_int - :lv_delta] = :la_level[:lv_int];
      la_node_name_tmp[:lv_int - :lv_delta] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_int - :lv_delta] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_int - :lv_delta] = :la_node_detail[:lv_int];
    
    -- Else Right > node_right => Right = Right - delta
    elseif :la_right[:lv_int] > :lv_node_right then
      la_left_tmp[:lv_int] = :lv_int;
      la_right_tmp[:lv_int] = :la_right[:lv_int] - :lv_delta;
      la_level_tmp[:lv_int] = :la_level[:lv_int];
      la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];

    -- Else Right < node_left => copy
    elseif :la_right[:lv_int] < :lv_node_left then
      la_left_tmp[:lv_int] = :lv_int;
      la_right_tmp[:lv_int] = :la_right[:lv_int];
      la_level_tmp[:lv_int] = :la_level[:lv_int];
      la_node_name_tmp[:lv_int] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_int] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_int] = :la_node_detail[:lv_int];
    end if;
  end for;
  
  
  -- Output
  out_hierarchy = UNNEST(:la_left_tmp, :la_right_tmp, :la_level_tmp, :la_node_name_tmp, :la_node_content_tmp, :la_node_detail_tmp) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

09 HAT_HIERARCHY_GET_COUNT

Get the size of the hierarchy = maximal LEFT value.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_COUNT;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_COUNT
(
  in in_hierarchy table ( 
    "LEFT" INTEGER,
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  out out_count INTEGER
) language sqlscript
sql security invoker as
BEGIN

  declare la_left integer array;
  
  la_left = ARRAY_AGG(:in_hierarchy."LEFT");
  
  out_count = CARDINALITY(:la_left);
END;

10 HAT_HIERARCHY_UPDATE_NODE

Update node:

• name
• content
• detail

based on node ID.

DROP procedure "PMYNARIK".HAT_HIERARCHY_UPDATE_NODE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_UPDATE_NODE
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  in in_node_id INTEGER,
  in in_node_name VARCHAR(5000),
  in in_node_content nclob,
  in in_node_detail_id INTEGER default 0,
  out out_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(255),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(255), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  
  
  -- Default
  lt_hierarchy = select * from :in_hierarchy;-- <= Important, do not change!
  
  
  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!


  -- Update values
  if :la_left[:in_node_id] is not null then
    la_node_name[:in_node_id] = :in_node_name;
    la_node_content[:in_node_id] = :in_node_content;
    la_node_detail[:in_node_id] = :in_node_detail_id;
  end if;
  

  -- Output
  out_hierarchy = UNNEST(:la_left, :la_right, :la_level, :la_node_name, :la_node_content, :la_node_detail) AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

11 HAT_HIERARCHY_GET_NODES_BY_NODE_DETAIL

Get all nodes based on node detail.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GET_NODES_BY_NODE_DETAIL;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GET_NODES_BY_NODE_DETAIL
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  in in_node_detail INTEGER,
  in in_check_existence TINYINT default 1,
  out out_nodes table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare lv_node_detail INTEGER;


  -- Check if node exists
  if in_check_existence = 1 then
    select "NODE_DETAIL" into lv_node_detail
      from :in_hierarchy
      where "NODE_DETAIL" = :in_node_detail;
  end if;


  -- Get node
  out_nodes = select * from :in_hierarchy
    where "NODE_DETAIL" = :in_node_detail;
END;

90 HAT_HIERARCHY_SAVE

DROP procedure "PMYNARIK".HAT_HIERARCHY_SAVE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_SAVE
(
  in in_hierarchy_id VARCHAR(5000),
  in in_hierarchy table ( 
    "LEFT" INTEGER,
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  out out_result tinyint
) language sqlscript
sql security invoker as
BEGIN

  declare lv_count integer;
  

  -- Delete the old version
  select count(*) into lv_count from "PMYNARIK".HAT_HIERARCHY
    where "HIERARCHY_ID" = :in_hierarchy_id;
  if lv_count > 0 then
    delete from "PMYNARIK".HAT_HIERARCHY
      where "HIERARCHY_ID" = :in_hierarchy_id;
  end if;


  -- Insert values
  insert into "PMYNARIK".HAT_HIERARCHY
    select
    :in_hierarchy_id as "HIERARCHY_ID",
    "LEFT",
    "RIGHT", 
    "LEVEL", 
    "NODE_NAME",
    "NODE_CONTENT", 
    "NODE_DETAIL"
    from :in_hierarchy;
  

  -- Get node
  out_result = 0;
END;

91 HAT_HIERARCHY_VISUALIZE

Show the visualization of the hierarchy in the text form.

DROP procedure "PMYNARIK".HAT_HIERARCHY_VISUALIZE;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_VISUALIZE
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER),
  out out_visualization table (
    "VISUALIZATION" nclob,
    "LEFT" INTEGER,
    "RIGHT" INTEGER,
    "LEVEL" INTEGER,
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN

  declare la_visual nclob array;
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare lv_int integer;
  declare lv_card integer;
  declare lv_len integer;
  declare lv_int_1 integer;
  
  declare lv_int_child integer;
  declare lv_start_child integer;
  declare lv_end_child integer;
  declare lv_most_right_child_left integer;

  declare lv_separator varchar(255) default '   ';
  declare lv_parent_separator varchar(255) default '';
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
  

  -- Default
  lt_hierarchy = select * from :in_hierarchy;-- <= Important, do not change!
  /* 
    The lt_hierarchy table must have all the indexes also for the null values
    Otherwise, the recalculation has to be implemented!
  */
  
  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");	
  -- array index = LEFT value!	

  
  -- Process
  lv_card = CARDINALITY(:la_left);
  for lv_int in 1 .. :lv_card do
    if :la_left[:lv_int] is null then
      continue;
    end if;
    
    
    -- Init text
    if :la_visual[:lv_int] is null then
      la_visual[:lv_int] = '';
    end if;
    
    
    -- Calculate spaces for parent's node name
    lv_parent_separator = '';
    lv_len = length(:la_node_name[:lv_int]);
    for lv_int_1 in 1 .. lv_len do
      lv_parent_separator = :lv_parent_separator || ' ';
    end for;
    
    
    -- Update the Current node
    if :lv_int = 1 then
      la_visual[:lv_int] = :la_visual[:lv_int] || :la_node_name[:lv_int];
    else
      la_visual[:lv_int] = :la_visual[:lv_int] || '-' || :la_node_name[:lv_int];
    end if;
    
    
    -- Start / End child borders
    lv_start_child = :lv_int + 1;
    lv_end_child = :la_right[:lv_int] - 1;
    
    
    -- Find the most right children
    lv_most_right_child_left = 0;
    if :lv_start_child < :lv_end_child then
      for lv_int_child in :lv_start_child .. :lv_end_child do
        if :la_left[:lv_int_child] is null then
          continue;
        end if;
        
        if :la_right[:lv_int_child] = :lv_end_child then
          lv_most_right_child_left = :lv_int_child;
          break;
        end if;
      end for;
    end if;
    
    
    -- Update the children
    if :lv_start_child < :lv_end_child then
        
      -- Go through all the children
      for lv_int_child in :lv_start_child .. :lv_end_child do
        if :la_left[:lv_int_child] is null then
          continue;
        end if;
        
        
        -- Check if null
        if :la_visual[:lv_int_child] is null then
          la_visual[:lv_int_child] = '';
        end if;
        
        
        if :la_level[:lv_int_child] = (:la_level[:lv_int] + 1) then				
        -- Update only direct child
          la_visual[:lv_int_child] = :la_visual[:lv_int_child] || :lv_parent_separator || '+';
          
        elseif :lv_int_child <= :lv_most_right_child_left then
        -- Update 
          la_visual[:lv_int_child] = :la_visual[:lv_int_child] || :lv_parent_separator || '|' || :lv_separator;
          
        else
        -- Update all the children
          la_visual[:lv_int_child] = :la_visual[:lv_int_child] || :lv_parent_separator || :lv_separator;
        end if;
        
      end for;
    end if;
  end for;


  -- Show temp table
  out_visualization = UNNEST(:la_visual, :la_left, :la_right, :la_level, :la_node_name, :la_node_content, :la_node_detail) AS 
    ("VISUALIZATION", "LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;

92 HAT_HIERARCHY_TEST

Create a test hierarchy to present the coding principles.

DROP procedure "PMYNARIK".HAT_HIERARCHY_TEST;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_TEST
(

) language sqlscript
sql security invoker as
BEGIN
/* 
    The code is unnecessary complex to check all functionalities
    The hiearchy is following:
    ROOT
     |
     +-A
       |
       +-B
       | |
       | +-C
       |   |
       |   +-D
       |
       +-E
       |
       +-F
         |
         +-G
           |
           +-H
*/
  
  declare lv_node_id integer;
  declare ls_node table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER);
  declare lt_hierarchy_out table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER);
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER);
  declare lt_visualization table ("VISUALIZATION" varchar(1000),
    "LEFT" INTEGER,
    "RIGHT" INTEGER,
    "LEVEL" INTEGER,
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);	
    
    
    
  declare lt_children table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER);
    
  
  
  -- Initialization
  call "PMYNARIK".HAT_HIERARCHY_INIT(lt_hierarchy);
  
  
  -- Add node A
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, 1, 'A', '', 0, lt_hierarchy, lv_node_id);
  -- Get ID of node A based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'A', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  
  -- Add node B
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'B', '', 0, lt_hierarchy, lv_node_id);	
  -- Get ID of node B based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'B', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;


  -- Add node C
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'C', '', 0, lt_hierarchy, lv_node_id);
  -- Get ID of node C based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'C', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;


  -- Add node D
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'D', '', 0, lt_hierarchy, lv_node_id);
  --lt_hierarchy_in = select * from :lt_hierarchy_out;
  
  -- Get ID of node A based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'A', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Add node E
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'E', '', 0, lt_hierarchy, lv_node_id);
  
  -- Get ID of node A based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'A', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Add node F
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'F', '', 0, lt_hierarchy, lv_node_id);
  -- Get ID of node F based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'F', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Add node G
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'G', '', 0, lt_hierarchy, lv_node_id);
  
  -- Get ID of node F based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'F', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Add node H
  call "PMYNARIK".HAT_HIERARCHY_ADD_NODE(:lt_hierarchy, :lv_node_id, 'H', '', 0, lt_hierarchy, lv_node_id);	



  
  -- Visualize
  call "PMYNARIK".HAT_HIERARCHY_VISUALIZE(:lt_hierarchy, lt_visualization);
  select * from :lt_visualization where "LEFT" is not null order by "LEFT";
  
  
  
  
  -- Get ID of node F based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'F', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Delete node F
  call "PMYNARIK".HAT_HIERARCHY_DELETE_NODE(:lt_hierarchy, :lv_node_id, lt_hierarchy);
  --select * from :lt_hierarchy;
  
  
  -- Get ID of node D based on name
  call "PMYNARIK".HAT_HIERARCHY_GET_NODES(:lt_hierarchy, 'D', 1, ls_node);
  select "LEFT" into lv_node_id from :ls_node;
  
  -- Delete node D
  call "PMYNARIK".HAT_HIERARCHY_DELETE_NODE(:lt_hierarchy, :lv_node_id, lt_hierarchy);
  
  
  
  
  -- Visualize
  call "PMYNARIK".HAT_HIERARCHY_VISUALIZE(:lt_hierarchy, lt_visualization);
  select * from :lt_visualization where "LEFT" is not null order by "LEFT";

END;

93 HAT_HIERARCHY_GENERATE_FROM_DATA

In case you have data in the standard recursive hierarchy format with a Node and its Parent, this procedure will create the nested set hierarchy implementation. It is very useful from the performance point of view.

DROP procedure "PMYNARIK".HAT_HIERARCHY_GENERATE_FROM_DATA;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_GENERATE_FROM_DATA
(
  in in_hierarchy_data table (
    "NODE_ID" INTEGER,
    "PARENT_ID" INTEGER
  ),
  out out_hierarchy table (
    "NODE_ID" INTEGER,
    "PARENT_ID" INTEGER,
    "LEFT" INTEGER,
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER),
  out out_return tinyint
) language sqlscript
sql security invoker as
BEGIN
/*
 Input hierarchy data table must: 
  - contain only one main ROOT node with node ID = 1
  - two parents cannot have the same child
*/
  
  declare la_data_node_id integer array;
  declare la_data_parent_id integer array;
  declare la_data_left integer array;
  declare la_data_right integer array;
  declare la_data_level integer array;
  
  declare la_data_children integer array;
  
  declare lt_hierarchy_data table (
    "NODE_ID" INTEGER,
    "PARENT_ID" INTEGER
    );
  
  declare lv_data_int integer;
  declare lv_data_card integer;
  
  declare lv_int integer;
  declare lv_row integer;
  
  declare lv_left integer;
  declare lv_right integer;
  declare lv_level integer;
  declare lv_children integer;
  declare lv_current_node integer;
  declare lv_stop tinyint;
  
  declare lv_data_parent_left integer;
  declare lv_data_parent_right integer;
  declare lv_data_parent_level integer;
  
  
  -- Default
  out_return = 0;
  lt_hierarchy_data = select * from :in_hierarchy_data order by "NODE_ID", "PARENT_ID";
  
  
  -- Set arrays
  la_data_node_id = ARRAY_AGG(:lt_hierarchy_data."NODE_ID");
  la_data_parent_id = ARRAY_AGG(:lt_hierarchy_data."PARENT_ID");

  
  -- Proceed
  lv_data_card = CARDINALITY(:la_data_node_id);
  
  
  -- Set ROOT node
  lv_children = :lv_data_card;
  la_data_left[1] = 1;
  la_data_level[1] = 1;
  la_data_right[1] = (:lv_children * 2) + 1;
  for lv_data_int in 2 .. :lv_data_card do
    -- Default
    lv_data_parent_left = 0;
    lv_data_parent_right = 0;
    lv_data_parent_level = 0;
    lv_left = 0;
    lv_right = 0;
    lv_level = 0;
    
    
    -- Get data parent node
    for lv_int in 1 .. :lv_data_card do
      if :la_data_node_id[:lv_int] = :la_data_parent_id[:lv_data_int] then
        lv_data_parent_left = :la_data_left[:lv_int];
        lv_data_parent_right = :la_data_right[:lv_int];
        lv_data_parent_level = :la_data_level[:lv_int];
        break;
      end if;
    end for;
    
    if :lv_data_parent_left = 0 then
      out_return = 1; -- data error, parent not found. Should not happen!
      return;
    end if;
    
    -- Set level
    lv_level = :lv_data_parent_level + 1;
    
    
    
    
    -- Get all children for the current node
    la_data_children = ARRAY();
    la_data_children[1] = :la_data_node_id[:lv_data_int];
    lv_row = 1;
    lv_stop = 0;
    while(:lv_stop = 0) do 
      lv_current_node = :la_data_children[:lv_row];
      
      for lv_int in 1 .. :lv_data_card do
        if :la_data_parent_id[:lv_int] = :lv_current_node then
          la_data_children[(CARDINALITY(:la_data_children) + 1)] = :la_data_node_id[:lv_int];
        end if;
      end for;
      
      lv_row = :lv_row + 1;
      if :lv_row > (CARDINALITY(:la_data_children)) then
        lv_stop = 1;
      end if;
    end while;
    
    -- Set children
    lv_children = CARDINALITY(:la_data_children) - 1;
    
    
    
    
    -- Is there Left sibling in the given level?		
    for lv_int in 1 .. :lv_data_card do
      if :la_data_left[:lv_int] = 0 then
        -- we go one by one from bottom to top
        -- therefore when Left = 0 we can stop search
        break;
      end if;
      
      if :la_data_level[:lv_int] = (:lv_data_parent_level + 1)
        and :la_data_left[:lv_int] > :lv_data_parent_left
        and :la_data_right[:lv_int] < :lv_data_parent_right
        and :la_data_right[:lv_int] > :lv_left
        then
        
        lv_left = :la_data_right[:lv_int] + 1;
        lv_right = :lv_left + (:lv_children * 2) + 1;
      end if;
    end for;
    
    
    -- Set the new node		
    if :lv_left = 0 then
      -- Based on parent
      la_data_left[:lv_data_int] = :lv_data_parent_left + 1;
      la_data_right[:lv_data_int] = (:lv_data_parent_left + 1) + (:lv_children * 2) + 1;
      la_data_level[:lv_data_int] = :lv_level;
      
    else
      -- Based on sibling
      la_data_left[:lv_data_int] = :lv_left;
      la_data_right[:lv_data_int] = :lv_right;
      la_data_level[:lv_data_int] = :lv_level;
    end if;
  end for;
  
  
  
  
  -- Output
  out_hierarchy = UNNEST(:la_data_node_id, :la_data_parent_id, :la_data_left, :la_data_right, :la_data_level)
    AS ("NODE_ID", "PARENT_ID", "LEFT", "RIGHT", "LEVEL");
END;

94 HAT_HIERARCHY_REARRANGE_LEFT

The whole solution is using arrays with indexes with the same value as the Left value. This will create Null values for indexes, which are not used. If the Null values are lost in the table, this procedure will recreate them.

DROP procedure "PMYNARIK".HAT_HIERARCHY_REARRANGE_LEFT;
CREATE PROCEDURE "PMYNARIK".HAT_HIERARCHY_REARRANGE_LEFT
(
  in in_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER),
  out out_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(5000),
    "NODE_CONTENT" nclob, 
    "NODE_DETAIL" INTEGER)
) language sqlscript
sql security invoker as
BEGIN
/*
 This will recalculate LEFT numbers in case NULL lines are not included
 It means the IDs will correspond with LEFT
*/
  
  declare la_left integer array;
  declare la_right integer array;
  declare la_level integer array;
  declare la_node_name VARCHAR(5000) array;
  declare la_node_content nclob array;
  declare la_node_detail integer array;
  
  declare la_left_tmp integer array;
  declare la_right_tmp integer array;
  declare la_level_tmp integer array;
  declare la_node_name_tmp VARCHAR(5000) array;
  declare la_node_content_tmp nclob array;
  declare la_node_detail_tmp integer array;
  
  declare lt_hierarchy table (
    "LEFT" INTEGER, 
    "RIGHT" INTEGER, 
    "LEVEL" INTEGER, 
    "NODE_NAME" VARCHAR(255), 
    "NODE_CONTENT" nclob,
    "NODE_DETAIL" INTEGER);
    
  declare lv_int integer;
  declare lv_max integer;
  declare lv_left integer;
  
  
  -- Default
  lt_hierarchy = select * from :in_hierarchy order by "LEFT";
  
  
  -- Set arrays
  la_left = ARRAY_AGG(:lt_hierarchy."LEFT");
  la_right = ARRAY_AGG(:lt_hierarchy."RIGHT");
  la_level = ARRAY_AGG(:lt_hierarchy."LEVEL");
  la_node_name = ARRAY_AGG(:lt_hierarchy."NODE_NAME");
  la_node_content = ARRAY_AGG(:lt_hierarchy."NODE_CONTENT");
  la_node_detail = ARRAY_AGG(:lt_hierarchy."NODE_DETAIL");
  
  
  -- Rearrange
  select max("LEFT") into lv_max from :lt_hierarchy;
  for lv_int in 1 .. :lv_max do
    lv_left = :la_left[:lv_int];
    if :lv_left is not null then		
      la_left_tmp[:lv_left] = :lv_left;
      la_right_tmp[:lv_left] = :la_right[:lv_int];
      la_level_tmp[:lv_left] = :la_level[:lv_int];
      la_node_name_tmp[:lv_left] = :la_node_name[:lv_int];
      la_node_content_tmp[:lv_left] = :la_node_content[:lv_int];
      la_node_detail_tmp[:lv_left] = :la_node_detail[:lv_int];
    end if;
  end for;
  
  
  -- Output
  out_hierarchy = UNNEST(:la_left_tmp, :la_right_tmp, :la_level_tmp, :la_node_name_tmp, :la_node_content_tmp, :la_node_detail_tmp) 
    AS ("LEFT", "RIGHT", "LEVEL", "NODE_NAME", "NODE_CONTENT", "NODE_DETAIL");
END;