CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-query
joe
joe

Reputation: 173

Tree Hierarchy in Oracle

I just came across this question from 'HackerRank'. The BST table contains two columns, N and P. N stands for Node, P stands for Parent:

 N  P
-- --
 1  2
 3  2
 6  8
 9  8
 2  5
 8  5
 5  null

Sample Output should be:

1 leaf
2 inner
3 leaf
5 root
6 leaf
8 inner
9 leaf

What I have tried is : 

select     N, 
           case 
               when level = 3 then 'leaf'
               when level = 2 then 'inner'
               else 'root'
           end 
from       BST
start with P is NULL
connect by P = prior N
order by   N;

Although it gives the correct result, I am not happy with the code as it hardcodes the name (whether it should be leaf, inner or root). Also this code will fail when there are multiple hierarchies in the tree.

Can someone please suggest any other elegant way of writing the same code so that it does not fail for multiple hierarchy?

Upvotes: 1

Views: 1115

Answers (3)

APC
APC

Reputation: 146179

Oracle provides a pseudocolumn CONNECT_BY_ISLEAF to test for leafiness. The test for root is the same as the START WITH clause. Anything else is inner. So this should do you

select N , 
    case 
        when CONNECT_BY_ISLEAF =  1 then 'leaf'
        WHEN P is NULL then 'root' 
        else 'inner'
    end 
from BST
start with P is NULL
connect by P = prior  N
order  by N;

Upvotes: 3

Singh Kailash
Singh Kailash

Reputation: 674

Please user Like below (For MS SQL Hierarchy)

;WITH Hierarchy(ChildId, Generation, ParentId) AS ( SELECT Id, 0 AS Generation, ParentId FROM Table_Name AS FirtGeneration WHERE ParentId IS NULL
UNION ALL SELECT NextGeneration.Id, Parent.Generation + 1, Parent.ChildId FROM Table_Name AS NextGeneration INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId
)

SELECT *FROM Hierarchy ORDER BY Generation;

Upvotes: 0

trincot
trincot

Reputation: 349908

You can make use of the connect_by_isleaf pseudocolumn, and the fact that the root will always have level 1:

select      n,
            case 
                when connect_by_isleaf = 1 then 'leaf'
                when level = 1 then 'root'
                else 'inner'
            end 
from        bst
start with  p is null
connect by  p = prior n
order by    n;

Upvotes: 4

Related Questions