Forum Discussion

Arpit65's avatar
Arpit65
Copper Contributor
Sep 28, 2022

Generating hierarchy table with levels representing the Parent, child, hierarchy levels and Path

So far i am using this query but this query is not able to generate correct rows if levels are increased in the hierarchy.

 CREATE OR REPLACE TABLE Hierarchy
("NodeID" varchar, "ParentID" varchar,"NodeName" varchar);

 Insert into "SCRIPT_TEST"."PUBLIC"."HIERARCHY"
("NodeID", "ParentID","NodeName")

VALUES
('1','4','London'),

('2','3','Munich'),

('3','5','Germany'),

('4','5','UK'),

('5','', 'Europe');

--Query1:

with recursive CteTree as

(Select "NodeID","ParentID","NodeName" as "NodeName1",

         CAST (NULL AS varchar(255)) as "NodeName2",

         CAST (NULL AS varchar(255)) as "NodeName3",

         0 as NodeName

         from "SCRIPT_TEST"."PUBLIC"."HIERARCHY" Where "ParentID" is not null



         Select child."NodeID", child."ParentID", "NodeName1",

         Case When NodeName+1 = 1 then "NodeName" else "NodeName2" end,

         Case When NodeName+1 = 2 then "NodeName" else "NodeName3" end,

         NodeName+1

         from CteTree



         UNION all



         Select "NodeID", "ParentID", "NodeName" as "NodeName1",

         Case When NodeName1+1 = 1 then "NodeName1" else "NodeName2" end,

         Case When NodeName1+1 = 2 then "NodeName1" else "NodeName3" end,

         NodeName+1

         from "SCRIPT_TEST"."PUBLIC"."HIERARCHY" )
        select distinct * from CteTree order by "NodeName1"

         Join "SCRIPT_TEST"."PUBLIC"."HIERARCHY" child

         ON child."ParentID" = CteTree."NodeID"

)

select distinct * from CteTree order by "NodeName1"

)

select distinct * from CteTree order by "NodeName1"

 

"In this query the values of level2 are also coming in level1 "

 

 

Query2:

with Hier (NodeID, ParentID,NodeName) as 
(select * from VALUES
('1','4','London'),
('2','3','Munich'),
('3','5','Germany'),
('4','5','UK'),
('5','',  'Europe'))   

select * from (select nodeid,parentid,path,index, max(value) 
over (partition by nodeid, index) CT from

(select sys_connect_by_path(NodeName, '\\')
 path,nodeid,parentid from Hier 
START WITH ParentID='' connect by PRIOR NodeID = ParentID),
table(split_to_table(path, '\\')))

 pivot(max(CT) for index in (2,3,4)) as answer (nodeid,parentid,path,nodename1,nodename2,nodename3)

"Query 2 is working but unable to produce result if levels are increased"

 

How this can be achieved? Creating the hierarchy tables with levels and Path. (Level are known for the Hierarchy)

No RepliesBe the first to reply

Resources