Skip to Content
-1
Aug 16, 2017 at 10:59 AM

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

494 Views Last edit Aug 16, 2017 at 03:07 PM 4 rev

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

Attachments

hv1.jpg (120.1 kB)
hv2.jpg (19.1 kB)