I need to export (using SQL) a PeopleSoft tree – showing all Tree Nodes and Leafs from the Top of the tree all the way down. levels are not used on the tree I am trying to retrieve. Can someone please help – it would be much appreciated!
Here is the SQL I use to get the Parent Node, Node, Detail Value (Leaf), and Level from trees in PeopleSoft via SQL:
Note: Click View Plain in the codeblock below to see all the SQL correctly. I’m working on my current WP theme to get it to quit interfering with my syntax highlighter.
SELECT PSTREENODE.SETID,
PSTREENODE.TREE_NAME,
PSTREENODE.EFFDT,
PARENT_NODE_NAME AS PARENTNODE,
TREE_NODE AS NODENAME,
TREE_NODE AS TREENODE,
TREE_LEVEL_NUM,
TREE_NODE_NUM
FROM PSTREENODE
--- REPLACE WITH YOUR SETID
WHERE PSTREENODE.SETID = 'SHARE'
--- REPLACE WITH YOUR TREE NAME
AND TREE_NAME = 'ACCOUNT'
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
AND EFFDT = TO_DATE('01011900','MMDDYYYY')
UNION ALL
--- REPLACE WITH YOUR SETID
SELECT 'SHARE',
N.TREE_NAME,
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
TO_DATE('01011900','MMDDYYYY'),
N.TREE_NODE AS PARENTNODE,
A.DESCR AS NODENAME,
--- REPLACE A.ACCOUNT WITH A.your_chartfield
A.ACCOUNT AS TREENODE,
TREE_LEVEL_NUM,
N.TREE_NODE_NUM
FROM PSTREENODE N
INNER JOIN PSTREELEAF L
ON N.SETID = L.SETID
AND N.TREE_NAME = L.TREE_NAME
--- REPLACE WITH YOUR SETID
AND N.SETID = 'SHARE'
--- REPLACE WITH YOUR TREE NAME
AND N.TREE_NAME = 'ACCOUNT'
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
AND N.EFFDT = TO_DATE('01011900','MMDDYYYY')
AND N.EFFDT = L.EFFDT
AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
--- REPLACE TABLE WITH LOOKUP TABLE FOR YOUR chartfield
INNER JOIN PS_GL_ACCOUNT_TBL A
--- REPLACE A.ACCOUNT WITH A.your_chartfield
ON A.ACCOUNT >= L.RANGE_FROM
--- REPLACE A.ACCOUNT WITH A.your_chartfield
AND A.ACCOUNT <= L.RANGE_TO
--- REPLACE WITH YOUR SETID
AND L.SETID = 'SHARE'
ORDER BY TREE_NODE_NUM,
TREE_LEVEL_NUM,
TREENODE;
Remember to read the comments on each line to see where and what you need to replace in the query, depending on the tree you are querying and the ChartField stored on the tree.
Hi Tyson,
I am using the above sql to retrieve the parent node,child nodes(till the last level). But when the table PSTREELEAF is empty. so unable to retrieve data. Is there any other table that store this inforamtion?
Thanks,
S
Sri,
Certainly. I assume your tree just has nodes, no detail values/leafs? If this is the case, and you just want to get the structure, the following SQL should work:
SELECT PSTREENODE.SETID, PSTREENODE.TREE_NAME, PSTREENODE.EFFDT, PARENT_NODE_NAME AS PARENTNODE, TREE_NODE AS NODENAME, TREE_NODE AS TREENODE, TREE_LEVEL_NUM, TREE_NODE_NUM FROM PSTREENODE --- REPLACE WITH YOUR SETID WHERE PSTREENODE.SETID = 'SHARE' --- REPLACE WITH YOUR TREE NAME AND TREE_NAME = 'ACCOUNT' --- REPLACE WITH YOUR TREE EFFECTIVE DATE AND EFFDT = TO_DATE('01011900','MMDDYYYY') UNION ALL --- REPLACE WITH YOUR SETID SELECT 'SHARE', N.TREE_NAME, --- REPLACE WITH YOUR TREE EFFECTIVE DATE TO_DATE('01011900','MMDDYYYY'), N.TREE_NODE AS PARENTNODE, ' ', ' ', TREE_LEVEL_NUM, N.TREE_NODE_NUM FROM PSTREENODE N --- REPLACE WITH YOUR TREE NAME WHERE TREE_NAME = 'ACCOUNT' --- REPLACE WITH YOUR TREE EFFECTIVE DATE AND EFFDT = TO_DATE('01011900','MMDDYYYY') ORDER BY TREE_NODE_NUM, TREE_LEVEL_NUM, TREENODE;Let me know if you have any additional questions, or if you were looking for something else. Thanks!
-Tyson
Thank you tyson for reply.
the information I am looking for is:-
for example:-
level 1:- A
level 2:- B
level 3:- C.
Each employee is assigned to C.
The sql I am looking at is to retrieve A,B,C. So that I can generate a report for A.
can you let me know if I confused you.
Thanks,