Traversing the Dept Security tree

Your rating: None Average: 3.3 (4 votes)

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'

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Bart's picture
Bart (not verified)
Re: Traversing the Dept Security tree

Hello,
This query seems very interesting (I've never used "Connect by")

I tried the query in a PeopleSoft DEMO database with
* SETID = 'SHARE'
* start with = '13000'

I get an error: "ORA-01436: CONNECT BY loop in user data" (Oracle 10g2)

Does anyone has the same problem?

Kind regards, Bart

SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM
FROM PSTREENODE TR
WHERE TR.TREE_NAME = 'DEPT_SECURITY'
AND TR.SETID = 'SHARE'
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 = '13000'

Lepa's picture
User offline. Last seen 35 weeks 6 days ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Traversing the Dept Security tree

Hi Bart, I ran your select in both oracle 9 and 10 and didn't encounter any errors.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Bart's picture
Bart (not verified)
Re: Traversing the Dept Security tree

Hi Lepa,

strange...

1) DEV environment : one DEPT_SECURITY tree; multiple (9) effdt's
==> the first query runs without error, but I have to use a "distinct", otherwise I get the rows 9 times...

2) PeopleSoft DEMO environment: multiple DEPT_SECURITY tree (different SETID); multiple effdt's
==> the first query has a loop... but the next query works fine... something to do with effdt?

SELECT TREE_NODE , TREE_LEVEL_NUM ,
LPAD(' ',2*(LEVEL-1))||LEVEL||' - '||TREE_NODE AS TREE_NODE
FROM
(SELECT T.TREE_NODE, T.PARENT_NODE_NAME, T.TREE_LEVEL_NUM
FROM PSTREENODE T
WHERE T.EFFDT = (SELECT MAX(IA.EFFDT)
FROM PSTREENODE IA
WHERE T.SETID = IA.SETID
AND T.SETCNTRLVALUE = IA.SETCNTRLVALUE
AND T.TREE_NAME = IA.TREE_NAME
AND IA.EFFDT <= to_date(sysdate))
AND T.TREE_NAME = 'DEPT_SECURITY'
and T.setid = 'SHARE'
)
where LEVEL <= (6 - 3 + 1)
START WITH TREE_NODE = '13000'
CONNECT BY PARENT_NODE_NAME = PRIOR TREE_NODE

(based on peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/walking-the-tree-1420585 )

Guest's picture
Guest (not verified)
Re: Traversing the Dept Security tree

Hi abhi10aug,
TO_DATE (SYSDATE, 'DD/MM/YY') seem to be wrong in my PeopleSoft Environment ,so I try the query using TR_ED.EFFDT <= SYSDATE,it succeed now.
.

Guest's picture
Guest (not verified)
Re: Traversing the Dept Security tree

Hi Bart,
I get the same error in my peoplesoft environment,and I test the above sql,its ok.
thanks share.

forex robot's picture
forex robot (not verified)
nice post. thanks

Amazing as always :)

Stan the Man's picture
Stan the Man (not verified)
Re: Traversing the Dept Security tree

Thanks for the posting. We used a variant of this to traverse the QEURY trees. It was quite useful during an upgrade where they got clobbered and we needed to re-apply our changes to the delivered query trees.
select
decode(ptn.parent_node_num,1,'section',' ')
,ptn.tree_node
,ptn.tree_node_num
from pstreenode ptn
where ptn.tree_name = 'QUERY_TREE_AP'
and ptn.effdt = (select max(ptn1.effdt)
from pstreenode ptn1
where ptn.setid=ptn1.setid
and ptn.tree_name=ptn1.tree_name
and ptn.tree_branch=ptn1.tree_branch
and ptn.tree_node = ptn1.tree_node
and ptn1.effdt <= SYSDATE)
CONNECT BY PRIOR ptn.TREE_NODE_NUM = ptn.PARENT_NODE_NUM
START WITH ptn.TREE_NODE_NUM = 1