CONNECT BY PRIOR / Hierarchical Queries / Query Tree Structure

Example 1:

SELECT  handling_unit_id, t.shipment_id
FROM    handling_unit_tab t
START   WITH handling_unit_id = '4558'
CONNECT BY PRIOR handling_unit_id = parent_handling_unit_id;

Example 2:

SELECT LEVEL, parent_node_id, node_id
FROM   (SELECT parent_node_id, node_id
        FROM   estimate_node_tab
        WHERE  estimate_id          = 77
        AND    estimate_revision_no = 1)
START   WITH parent_node_id      = 0
CONNECT BY NOCYCLE PRIOR node_id = parent_node_id
ORDER   BY parent_node_id;

Example 3:

SELECT  level, SYS_CONNECT_BY_PATH(ename, '/') path
FROM    emp
START   WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Output:
LEVEL PATH
----- ---------------------------------------------------------------
1     /KING
2     /KING/JONES
3     /KING/JONES/SCOTT

Related Links:

Hierarchical Queries