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'

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

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 )

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.
.

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

Amazing as always :)

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