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'

 

转载于:https://www.cnblogs.com/GoDevil/archive/2009/07/27/1532085.html