Tree denoralization script for PeopleSoft trees --max 9 levels only

--> Script to denormalize the PeopleSoft tree with 9 levels max

 SELECT *
  FROM (
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,' ' LVL2
 ,' ' LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREELEAF lf
 WHERE T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T1.TREE_NODE_NUM = 1
   AND T1.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,' ' LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T2.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T3.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T4.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T5.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T6.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T7.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,T8.TREE_NODE LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREENODE T2
  ,PSTREENODE T3
  ,PSTREENODE T4
  ,PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREENODE T8
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.SETID = T8.SETID
   AND T1.TREE_NAME = T8.TREE_NAME
   AND T1.EFFDT = T8.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T7.TREE_NODE_NUM = T8.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T8.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,T8.TREE_NODE LVL8
 ,T9.TREE_NODE LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREENODE T2
  ,PSTREENODE T3
  ,PSTREENODE T4
  ,PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREENODE T8
  ,PSTREENODE T9
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.SETID = T8.SETID
   AND T1.TREE_NAME = T8.TREE_NAME
   AND T1.EFFDT = T8.EFFDT
   AND T1.SETID = T9.SETID
   AND T1.TREE_NAME = T9.TREE_NAME
   AND T1.EFFDT = T9.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T7.TREE_NODE_NUM = T8.PARENT_NODE_NUM
   AND T8.TREE_NODE_NUM = T9.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T9.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0 ) DNM
 WHERE DNM.TREE_NAME = 'ACCOUNT_GLOBAL'
   AND DNM.SETID = 'SHARE'
   AND DNM.EFFDT = '01-Jan-2008'