Flattening a hierarchial data set in Oracle

I have a set of data as shown below

TREENAME DATE       LEVEL PARENTNODE    CHILDNODE
GRN_KK   9/1/2012   1     null          ALLACCOUNT
GRN_KK   9/1/2012   2     ALLACCOUNT     52000
GRN_KK   9/1/2012   2     ALLACCOUNT     59900
GRN_KK   9/1/2012   3     52000       54311
GRN_KK   9/1/2012   3     52000       50100
GRN_KK   9/1/2012   3     52000       54345
GRN_KK   9/1/2012   3     52000       54346
GRN_KK   9/1/2012   3     52000       54347
GRN_KK   9/1/2012   4     50100       50151

I need to flatten this data as below:

GRN_KK         9/2/2012     ALLACCOUNT           52000
GRN_KK         9/2/2012     ALLACCOUNT           52000      54311
GRN_KK         9/2/2012     ALLACCOUNT           52000      50100
GRN_KK         9/2/2012     ALLACCOUNT           52000      50100     50151
GRN_KK         9/2/2012     ALLACCOUNT           59900

And so on.

I can have up to 30 parent child relationship

I used the connect by and sys_connect_by_path as follows

    select A.TREE_NAME,A.EfFDT,A.PARENT_NODE_NAME,A.TREE_NODE, sys_connect_by_path(A.TREE_NODE, '/')
FROM PSTREENODE A
WHERE A.TREE_NAME = 'MDA_GRN_KK'
AND A.EFFDT = ( 
 SELECT MAX(A1.EFFDT) 
  FROM PSTREENODE A1 
 WHERE A.SETID = A1.SETID 
   AND A.SETCNTRLVALUE = A1.SETCNTRLVALUE 
   AND A.TREE_NAME = A1.TREE_NAME 
   AND A.TREE_NODE_NUM = A1.TREE_NODE_NUM 
   AND A.TREE_NODE = A1.TREE_NODE 
   AND A.TREE_BRANCH = A1.TREE_BRANCH 
   AND A1.EFFDT <= sysdate)
connect by nocycle prior  A.TREE_NODE = A.PARENT_NODE_NAME;

The result set is too large. There are other trees in this table and I don’t think it just looks at the specified TREE_NAME. Its giving me all sorts of combinations.

Answer

If you want to see tree 'MDA_GRN_KK' then that’s the “root” of your hierarchy. You specify the root using the START WITH clause. I normally put it right before the CONNECT BY:

... beginning of your query ...
START WITH A.TREE_Name = 'MDA_GRN_KK'
connect by nocycle prior  A.TREE_NODE = A.PARENT_NODE_NAME

Advertisement

Answer