By using the PRIOR operator on the opposite column as before, we can query the path from a start node to the root node:
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Id=6
CONNECT BY PRIOR Parent = Id;
-- ^^^^^^^^^^^^
LEVEL ID NODE
---------- ---------- ----------------------
1 6 Node 1.2.1.1
2 5 ANode 1.2.1
3 4 Node 1.2
4 2 Node 1
5 1 Root Node
Alternatively, we can also use the SYS_CONNECT_BY_PATH() function to determine the path of each node up to the root node, while using the same query as we did in the previous sections for the normal hierarchical query:
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 25) Node, substr(SYS_CONNECT_BY_PATH(Label, ' => '), 0, 80) Path
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER SIBLINGS BY Label;
The first parameter of SYS_CONNECT_BY_PATH defines the column to use for each path component, and the second parameter defines the delimiter to use between each path component:
LEVEL ID NODE PATH
----- ----- ------------------------ ----------------------------------------------------------------
1 1 Root Node => Root Node
2 2 Node 1 => Root Node => Node 1
3 3 Node 1.1 => Root Node => Node 1 => Node 1.1
3 4 Node 1.2 => Root Node => Node 1 => Node 1.2
4 5 Node 1.2.1 => Root Node => Node 1 => Node 1.2 => Node 1.2.1
5 6 Node 1.2.1.1 => Root Node => Node 1 => Node 1.2 => Node 1.2.1 => Node 1.2.1.1
2 7 Node 2 => Root Node => Node 2
3 8 Node 2.1 => Root Node => Node 2 => Node 2.1
3 9 Node 2.3 => Root Node => Node 2 => Node 2.3
3 10 Node 2.4 => Root Node => Node 2 => Node 2.4
2 11 Node 3 => Root Node => Node 3
2 12 Node 4 => Root Node => Node 4
3 13 Node 4.1 => Root Node => Node 4 => Node 4.1