I am trying to replicate the result of a qlik function called Hierarchy. It creates the Hierarchy with different Levels and also gives the Hierarchy Path. The Code that i am using so far is giving me the Levels but with an error that Level2 values are also coming in Level1.

CREATE OR REPLACE TRANSIENT TABLE "Hierarchy" ( "NodeID" VARCHAR, "ParentID" VARCHAR, "NodeName" String)

Insert into "Hierarchy"("NodeID", "ParentID","NodeName")

VALUES
('1','4','London'),
('2','3','Munich'),
('3','5','Germany'),
('4','5','UK'),
('5','',  'Europe');


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 "Hierarchy"
                 Where "ParentID" is not null

                 UNION ALL

                 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

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

)

select distinct * from CteTree order by "NodeName1" 

The Output that it is producing:

Desired OutPut:

How can it be achieved?

CodePudding user response：

with Hierarchy (NodeID, ParentID,NodeName) as (select * from VALUES
 ('1','4','London'),
 ('2','3','Munich'),
 ('3','5','Germany'),
 ('4','5','UK'),
 ('5','',  'Europe'))   
                                                              
 select   
     sys_connect_by_path(NodeName, ' -> ') path 
    , NodeID
    , ParentID
    , NodeName  
 from 
      Hierarchy 
 START WITH ParentID ='' 
 CONNECT BY PRIOR NodeID =  ParentID ;

CodePudding user response：

CREATE TABLE HIERARCHYWITHLEVEL(NODEID, PARENTID, NODENAME, LEVEL)
AS
WITH TREE AS
(SELECT NODEID, PARENTID, NODENAME, 1 AS LEVEL
 FROM HIERARCHY
 WHERE PARENTID = ''
 UNION ALL
 SELECT HIERARCHY.NODEID, HIERARCHY.PARENTID, HIERARCHY.NODENAME, LEVEL   1
 FROM HIERARCHY
 JOIN TREE
 ON HIERARCHY.PARENTID = TREE.NODEID
)
SELECT NODEID, PARENTID, NODENAME, LEVEL
FROM TREE;