How to "unpack" HANA Hierarchy View to a more useful format?

[dmitry_sharshatkin]

Hello gurus!

I have created a hierarchy view on the table:

CREATE COLUMN TABLE XXXX.HIETEST ( pred CHAR(2), succ CHAR(2) PRIMARY KEY );
INSERT INTO XXXX.HIETEST VALUES ( null, 'A1' );
INSERT INTO XXXX.HIETEST VALUES ( 'A1', 'B1' );
INSERT INTO XXXX.HIETEST VALUES ( 'A1', 'B2' );
INSERT INTO XXXX.HIETEST VALUES ( 'B1', 'C1' );
INSERT INTO XXXX.HIETEST VALUES ( 'B1', 'C2' );
INSERT INTO XXXX.HIETEST VALUES ( 'B2', 'C3' );
INSERT INTO XXXX.HIETEST VALUES ( 'B2', 'C4' );
INSERT INTO XXXX.HIETEST VALUES ( 'C3', 'D1' );
INSERT INTO XXXX.HIETEST VALUES ( 'C3', 'D2' );
INSERT INTO XXXX.HIETEST VALUES ( 'C4', 'D3' );

DROP VIEW XXXX.HIETEST_V;

CREATE COLUMN VIEW XXXX.HIETEST_V TYPE HIERARCHY
WITH PARAMETERS ( 
'hierarchyDefinitionType' = 'select', 
'hierarchyDefinition' = '{
"sourceType":"recursive", 
"nodeType":"string", 
"runtimeObjectType":"blob", 
"sourceQuery":"SELECT pred, succ FROM XXXX.HIETEST " }' 
);

It has a structure as below:

In addition, i have a flat table, where some attributes are stored and the key is a QUERY_NODE from our hierarchy view.

CREATE COLUMN TABLE XXXX.REFDATA ( OBJECT CHAR(2), ATTRIBUTE CHAR(4) PRIMARY KEY );

INSERT INTO XXXX.REFDATA VALUES ( 'A1', 'A101' );
INSERT INTO XXXX.REFDATA VALUES ( 'A1', 'A102' );
INSERT INTO XXXX.REFDATA VALUES ( 'B1', 'B101' );
INSERT INTO XXXX.REFDATA VALUES ( 'B1', 'B102' );
INSERT INTO XXXX.REFDATA VALUES ( 'B2', 'B201' );
INSERT INTO XXXX.REFDATA VALUES ( 'B2', 'B202' );
INSERT INTO XXXX.REFDATA VALUES ( 'C1', '-C1-' );
INSERT INTO XXXX.REFDATA VALUES ( 'C2', '-C2-' );
INSERT INTO XXXX.REFDATA VALUES ( 'C3', '-C3-' );
INSERT INTO XXXX.REFDATA VALUES ( 'C3', '+C3+' );
INSERT INTO XXXX.REFDATA VALUES ( 'C4', '-C4-' );
INSERT INTO XXXX.REFDATA VALUES ( 'D1', '-D1-' );
INSERT INTO XXXX.REFDATA VALUES ( 'D2', '-D2-' );
INSERT INTO XXXX.REFDATA VALUES ( 'D3', '-D3-' );

Now I want to "unpack" the hierarchy view (field PATH) and join the attribute table in a way to get the following results:

I.e. for object A1, we collect entries from the attribute table related to only this object, as its PATH has only A1. But for the object B1, we collect records for the object A1 and B1, as they are contained in the PATH.

There would be no issues, if instead of string PATH, we would have a column.

I even tried to use a split function:

DROP FUNCTION SPLIT_PATH_F;

CREATE FUNCTION SPLIT_PATH_F (IP_STRING NVARCHAR(5000)) 
RETURNS TABLE (V_STRING NVARCHAR(100))
LANGUAGE SQLSCRIPT AS 
BEGIN 
declare _items nvarchar(100) ARRAY; 
declare _text nvarchar(100);
declare _index integer; 
_text := :IP_STRING; 
_index := 1; 
WHILE LOCATE(:_text,'/') > 0 DO 
 _items[:_index] := SUBSTR_BEFORE(:_text,'/');
 _text := SUBSTR_AFTER(:_text,'/'); 
 _index := :_index + 1; 
END WHILE; 
_items[:_index] := :_text; 
rst = UNNEST(:_items) AS ("V_STRING");
RETURN SELECT * FROM :rst; 
END; 

SELECT * FROM SPLIT_PATH_F('A1/B1/C1');

and a procedure:

DROP PROCEDURE SPLIT_PATH;

CREATE PROCEDURE SPLIT_PATH(TEXT nvarchar(100)) 
AS BEGIN 
declare _items nvarchar(100) ARRAY; 
declare _text nvarchar(100); 
declare _index integer; 
_text := :TEXT; 
_index := 1; 
WHILE LOCATE(:_text,'/') > 0 DO 
 _items[:_index] := SUBSTR_BEFORE(:_text,'/'); 
 _text := SUBSTR_AFTER(:_text,'/'); 
 _index := :_index + 1; 
END WHILE; 
_items[:_index] := :_text; 
rst = UNNEST(:_items) AS ("items"); 
SELECT * FROM :rst; 
END;

CALL SPLIT_PATH('A1/B1/C1');

But whatever I do, I always meet certain limitations, dozens of limitations...

Did anyone experienced and resolved same problems?

What would be the best way to "unpack" the PATH in HANA hierarchy view?

Is it possible at all?

Because right now, i would not say it's bringing very much value...

Thanks, Dima

[lbreddemann]

A couple of comments here: are you using HANA 1? If so, then the create hierarchy view is not supposed to be used as you do it. This feature had not been published in the SQL API reference. You might consider this as a hint towards, why there may be use cases for hierarchies that it does not support.

Look into HANA 2 as it provides public SQL commands for hierarchy processing.

Besides that, the last time I check the internal developer WIKI for the hierarchy view feature, there had been examples of how to ask hierarchy related questions like "get me all children of node XYZ". Maybe these examples (with their non SQL syntax) can work for you.

Anyhow, it is usually not the best choice to ask in a public forum for help with unpublished features.

[dmitry_sharshatkin · Accepted solution]

Found a kind of solution myself.

I'm two loops over the view and simply use LOCATE function to analyze the string PATH:

SELECT 
  T1.QUERY_NODE AS QUERY_NODE,
  T1.PATH       AS PATH,
  T2.QUERY_NODE AS CHILDS,
  T3.ATTRIBUTE  AS ATTRIBUTES
FROM 
 XXXX.HIETEST_V AS T1,
 XXXX.HIETEST_V AS T2
LEFT OUTER JOIN 
 XXXX.REFDATA   AS T3
ON
 T3.OBJECT = T2.QUERY_NODE 
WHERE
 LOCATE(T1.PATH, T2.QUERY_NODE) > 0
ORDER BY 
 QUERY_NODE,
 CHILDS

Not sure about the performance on the real data, but at least it works.

BR, Dima