Hierarchical structure query in HANA

Recently I want to use the hierarchical structure query in HANA. I just looked up on the Internet and said that the hierarchical query syntax of HANA and ORACLE is the same. Actually, I found that it can't be used at all. It is really speechless, and the content that has not been verified is spread everywhere, floating dry, floating dry.

I went to the official website for a long time to find that HANA clearly does not support Oracle's hierarchical query method. HANA's hierarchical query is implemented using a function called hierarchy. The related usage is as follows:

First construct two simple 2-layer structures

WITH t AS (SELECT '1' p,'1.1' c FROM dummy UNION SELECT '1' p,'1.2' c FROM dummy UNION SELECT '1' p,'1.3' c FROM dummy UNION SELECT '1.2' p,'1.2.1' c FROM dummy UNION SELECT '2' p,'2.1' c FROM dummy UNION SELECT '2' p,'2.2' c FROM dummy UNION SELECT '2' p,'2.3' c FROM dummy UNION SELECT '2.1' p,'2.1.1' c FROM dummy )SELECT * FROM hierarchy (SOURCE (SELECT p parent_id,c node_id FROM t)START WHERE p IN ('1','2'))

search result:

WITH t AS (SELECT '1' p,'1.1' c FROM dummy UNION SELECT '1' p,'1.2' c FROM dummy UNION SELECT '1' p,'1.3' c FROM dummy UNION SELECT '1.2' p,' 1.2.1' c FROM dummy UNION SELECT '2' p,'2.1' c FROM dummy UNION SELECT '2' p,'2.2' c FROM dummy UNION SELECT '2' p,'2.3' c FROM dummy UNION SELECT '2.1 'p,'2.1.1' c FROM dummy) SELECT * FROM hierarchy (SOURCE (SELECT p parent_id,c node_id FROM t) START WHERE p IN ('1','2'))
HIERARCHY_RANKHIERARCHY_TREE_SIZEHIERARCHY_PARENT_RANKHIERARCHY_ROOT_RANKHIERARCHY_LEVELHIERARCHY_IS_CYCLEHIERARCHY_IS_ORPHANPARENT_IDNODE_ID
110110011.1
220210011.2
31222001.21.2.1
410410011.3
520510022.1
61552002.12.1.1
710710022.2
810810022.3

Here the names of the parent node and the child node are fixed as parent_id and node_id. The hierarchy determines the parent node and the child node according to these two fields. The above figure shows the query from the upper layer to the lower layer. If you want to reverse the query, you can set the parent_id Just swap the name with node_id.

HIERARCHY_RANK: Node number

HIERARCHY_TREE_SIZE: the number of nodes in the current tree

HIERARCHY_PARENT_RANK: The parent node number of the current node

HIERARCHY_ROOT_RANK: The root node number of the current node

HIERARCHY_LEVEL: current node level

HIERARCHY_IS_CYCLE: I don’t know what it is

HIERARCHY_IS_ORPHAN: I don’t know what it is