Using HIERARCHY Functions in SAP HANA Express

[UxKjaer]

Hi all,

I'm trying to solve a little challenge i have. But i'm stuck and are hoping to get some help from the community.

I've got below below table and data.

CREATE TABLE t_demo_time ( parent_id VARCHAR(32), node_id VARCHAR(32), valid_from DATE, valid_until DATE );		

INSERT INTO t_demo_time  VALUES ( NULL, 'T1', '2011-01-01', '9999-12-31' );
INSERT INTO t_demo_time  VALUES ( 'T1', 'U1', '2011-07-01', '9999-12-31' );
INSERT INTO t_demo_time  VALUES ( 'U1', 'V1', '2011-07-01', '2012-12-31' );
INSERT INTO t_demo_time  VALUES ( 'V1', 'W1', '2011-08-01', '2012-12-31' );

INSERT INTO t_demo_time  VALUES ( 'U1', 'V2', '2013-01-01', '2016-12-31' );
INSERT INTO t_demo_time  VALUES ( 'V2', 'W2', '2013-01-01', '2016-12-31' );

INSERT INTO t_demo_time  VALUES ( 'T1', 'V1', '2013-01-01', '2016-12-31' );
INSERT INTO t_demo_time  VALUES ( 'V1', 'W1', '2013-01-01', '2016-12-31' );

I'm trying to create a parent-child hierarchy that is time dependant. As you can see on my data, V1 moves from parent U1 to T1 on the 01-01-2013.

I thought i could use the SQL functions to solve my problem. But so far i'm out of luck.

If i use the generic generator function then my row V1 with parent U1 suddenly belongs to parent T1.

If i try to use the HIERARCHY_TEMPORAL function which I thought would solve my problem, then i get an error:

SAP DBTech JDBC: [5089]: Hierarchy error: Internal error during hierarchy operation. push invalid time interval: line 10 col 6 (at pos 210)

This is my query that gave me the push error.

SELECT
    hierarchy_rank AS rank,
    hierarchy_tree_size AS tree_size,
    hierarchy_parent_rank AS parent_rank,
    hierarchy_level ASlevel,
    parent_id,
    node_id,
    valid_from,
    valid_until
FROM HIERARCHY_TEMPORAL (SOURCE t_demo_time
    SIBLING ORDERBY node_id, valid_from
    VALIDFROM'2011-01-01'UNTIL'2019-12-31');

I've also tried this one, but that doesn't give any output.

CREATELOCAL TEMPORARY COLUMNTABLE#h_demo_time AS(SELECT*FROM HIERARCHY (SOURCE t_demo_time
    SIBLING ORDERBY node_id )ORDERBY
    hierarchy_rank );select*from#h_demo_time;SELECT
    h2.hierarchy_rank AS rank,
    h2.hierarchy_tree_size AS tree_size,
    h2.hierarchy_parent_rank AS parent_rank,
    h2.hierarchy_level ASlevel,
    h2.parent_id,
    h2.node_id,
    h2.valid_from,
    h2.valid_until,
    h2.hierarchy_level - h1.hierarchy_level ASdistance,
    h1.hierarchy_rank AS start_rank
FROM#h_demo_time AS h1,#h_demo_time AS h2
WHERE
      h2.hierarchy_parent_rank = h1.hierarchy_rank
      AND h2.valid_from > h1.valid_from
      AND h2.valid_until < h1.valid_until
ORDERBY h2.hierarchy_rank;

Hope for someone helps

[pfefferf]

What HANA (express) version you are using?

Please add your statement which is using the hierarchy function to your question too.

[UxKjaer]

Hi florian.pfeffer,

I've updated my question with my SQL statements.

Thanks for your support

[pfefferf]

And what HANA version you are using?

In the statement using the HIERARCHY_TEMPORAL function, are the missing blanks for VALIDFROM'2011-01-01'UNTIL'2019-12-31'
are just a formatting issue when you copied the statement into here, or did you really tried execute that in that way?

[UxKjaer]

Yeah sorry that was a formatting issue.

[pfefferf]

Tested this on a HANA Express 2.0 SPS03 system with the data you described. For me it works fine. 3rd time the question: What HANA version you are using?

[UxKjaer]

Sorry, my version is HANA 2 sps 03. So should be alright. My hardware setup is seen below.

[former_member1716]

Hello,

I find that you have mentioned the field name incorrectly in your select statement, please double check that.

Also if you are intending to get values between a range of dates then you can try "BETWEEN" operator in your query.

Hope this helps.