Traversing the Dept Security tree

 

We can use theconnect by priorfunction 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.

SELECTTR.TREE_NODE,TR.TREE_LEVEL_NUM        

FROMPSTREENODE TR
WHERETR.TREE_NAME      = 'DEPT_SECURITY'
ANDTR.SETID                   = $SETID
ANDTR.EFFDT  = (SELECTMAX(TR_ED.EFFDT) FROMPSTREENODE TR_ED
WHERETR.SETID=TR_ED.SETID  AND  TR.TREE_NAME=TR_ED.TREE_NAME
AND  TR_ED.EFFDT<=TO_DATE(SYSDATE, 'DD/MM/YY'))
ANDLEVEL  <= (6 - 3 + 1)
CONNECTBYPRIOR TR.TREE_NODE=TR.PARENT_NODE_NAME
STARTWITHTR.TREE_NODE= 'LVL3-DPT'