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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
