Hierarchical Queries - Oracle

Questions

I have a table with many project id s and for each project ,I have hierarchical structure as below.

https://i.stack.imgur.com/vD0a0.png https://i.stack.imgur.com/vD0a0.png

My input is risk id..How can I get the top level parent for the input risk ID.

IF I give 25 as Input I should get 23 as output.how can I achieve this.

Answers

Starting from the required row and ascending to the root:

SELECT RiskID,
FROM   PROJECT_RISKS
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       RiskID    = 25
CONNECT BY
       PRIOR Link2 = Link1
AND    PRIOR ProjectID = ProjectID;

Starting from the root and descending to the required row:

SELECT  CONNECT_BY_ROOT( RiskID ) AS RiskID
FROM    Project_Risks
WHERE   RiskID    = 25
START WITH
        link2     IS NULL
CONNECT BY
       PRIOR Link1 = Link2
AND    PRIOR ProjectID = ProjectID;

Both will give the same answer.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/34066931/hierarchical-queries-oracle

Related