Traversing the Dept Security tree

 

We can use the connect by prior function provided by Oracle to traverse the Dept security Tree . Suppose an Organization has 7 levels in Dept tree i.e. 1 to 7 . We want to traverse the dept tree from level 3 to level 6. Also we know the deptid at the level 3 say LVL3-DPT .

We can use the following SQL to traverse the whole tree (3-6) starting from LVL3-DPT. Only child of LVL3-DPT will be seen in sql output. The starting deptid has to be used with "START WITH " clause.

SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM FROM PSTREENODE TR WHERE TR.TREE_NAME = DEPT_SECURITY AND TR.SETID = $SETID AND TR.EFFDT = (SELECT MAX(TR_ED.EFFDT) FROM PSTREENODE TR_ED WHERE TR.SETID = TR_ED.SETID AND TR.TREE_NAME = TR_ED.TREE_NAME AND TR_ED.EFFDT <= TO_DATE (SYSDATE, DD/MM/YY)) AND LEVEL <= (6 - 3 + 1) CONNECT BY PRIOR TR.TREE_NODE = TR.PARENT_NODE_NAME START WITH TR.TREE_NODE = LVL3-DPT

We can use the connect by prior function provided by Oracle to traverse the Dept security Tree . Suppose an Organization has 7 levels in Dept tree i.e. 1 to 7 . We want to traverse the dept tree from level 3 to level 6. Also we know the deptid at the level 3 say LVL3-DPT . We can use the following SQL to traverse the whole tree (3-6) starting from LVL3-DPT. Only child of LVL3-DPT will be seen in sql output. The starting deptid has to be used with "START WITH " clause. SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM FROM PSTREENODE TR WHERE TR.TREE_NAME = DEPT_SECURITY AND TR.SETID = $SETID AND TR.EFFDT = (SELECT MAX(TR_ED.EFFDT) FROM PSTREENODE TR_ED WHERE TR.SETID = TR_ED.SETID AND TR.TREE_NAME = TR_ED.TREE_NAME AND TR_ED.EFFDT <= TO_DATE (SYSDATE, DD/MM/YY)) AND LEVEL <= (6 - 3 + 1) CONNECT BY PRIOR TR.TREE_NODE = TR.PARENT_NODE_NAME START WITH TR.TREE_NODE = LVL3-DPT