Extract Trees via SQL

How to Extract PeopleSoft Trees into a CSV format.

If you have the need to pull tree information into Excel, use the following SQL to easily get the Tree info in a tree level style.  The SQL will also pull Leaf information if the tree has leafs.

This SQL only works on trees with 10 or fewer levels..

You will need to change the setid, tree_name and effdt.  Also, these fields are found twice in the SQL.

Oracle DB

SELECT SETID, TREE_NAME, EFFDT
, decode(TREE_LEVEL_NUM, 0, TREE_NODE, 1, TREE_NODE, '') "TN1"
, decode(TREE_LEVEL_NUM, 2, TREE_NODE, '') "TN2"
, decode(TREE_LEVEL_NUM, 3, TREE_NODE, '') "TN3"
, decode(TREE_LEVEL_NUM, 4, TREE_NODE, '') "TN4"
, decode(TREE_LEVEL_NUM, 5, TREE_NODE, '') "TN5"
, decode(TREE_LEVEL_NUM, 6, TREE_NODE, '') "TN6"
, decode(TREE_LEVEL_NUM, 7, TREE_NODE, '') "TN7"
, decode(TREE_LEVEL_NUM, 8, TREE_NODE, '') "TN8"
, decode(TREE_LEVEL_NUM, 9, TREE_NODE, '') "TN9"
, decode(TREE_LEVEL_NUM, 10, TREE_NODE, '') "TN10"
, '' "Range"
, TREE_NODE_NUM "Sort Field"
FROM PSTREENODE N
WHERE N.SETID = 'SHARE'
AND N.TREE_NAME = 'DEPT_SECURITY'
AND N.EFFDT = '01-JAN-2011'
UNION
SELECT L.SETID, L.TREE_NAME, L.EFFDT
, '', '', '', '', '', '', '', '', '', ''
, decode(l.dynamic_range, 'Y', '%', decode(L.RANGE_FROM, L.RANGE_TO, L.RANGE_FROM, L.RANGE_FROM || ' to ' || L.RANGE_TO))
, L.TREE_NODE_NUM + .1
FROM PSTREELEAF L, PSTREENODE N
WHERE N.SETID = 'SHARE'
AND N.TREE_NAME = 'DEPT_SECURITY'
AND N.EFFDT = '01-JAN-2011'
AND N.SETID = L.SETID AND N.SETCNTRLVALUE = L.SETCNTRLVALUE AND N.TREE_NAME = L.TREE_NAME AND N.EFFDT = L.EFFDT AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
ORDER BY 15, 14;

MS SQL Server DB

SELECT SETID, TREE_NAME, EFFDT
, CASE TREE_LEVEL_NUM WHEN '0'  THEN TREE_NODE WHEN '1' THEN TREE_NODE ELSE '' END AS "TN1"
, CASE TREE_LEVEL_NUM WHEN '2'  THEN TREE_NODE ELSE '' END AS "TN2"
, CASE TREE_LEVEL_NUM WHEN '3'  THEN TREE_NODE ELSE '' END AS "TN3"
, CASE TREE_LEVEL_NUM WHEN '4'  THEN TREE_NODE ELSE '' END AS "TN4"
, CASE TREE_LEVEL_NUM WHEN '5'  THEN TREE_NODE ELSE '' END AS "TN5"
, CASE TREE_LEVEL_NUM WHEN '6'  THEN TREE_NODE ELSE '' END AS "TN6"
, CASE TREE_LEVEL_NUM WHEN '7'  THEN TREE_NODE ELSE '' END AS "TN7"
, CASE TREE_LEVEL_NUM WHEN '8'  THEN TREE_NODE ELSE '' END AS "TN8"
, CASE TREE_LEVEL_NUM WHEN '9'  THEN TREE_NODE ELSE '' END AS "TN9"
, CASE TREE_LEVEL_NUM WHEN '10' THEN TREE_NODE ELSE '' END AS "TN10"
, '' "Range"
, TREE_NODE_NUM "Sort Field"
FROM PSTREENODE N
WHERE N.SETID = 'SHARE'
AND N.TREE_NAME = 'ACCOUNTS'
AND N.EFFDT = '01-JAN-1901'
UNION
SELECT L.SETID, L.TREE_NAME, L.EFFDT
, '', '', '', '', '', '', '', '', '', ''
, CASE L.DYNAMIC_RANGE WHEN 'Y' THEN '%' ELSE CASE L.RANGE_FROM WHEN L.RANGE_TO THEN RTRIM(L.RANGE_FROM) ELSE RTRIM(L.RANGE_FROM) + ' to ' + RTRIM(L.RANGE_TO) END END
, L.TREE_NODE_NUM + .1
FROM PSTREELEAF L, PSTREENODE N
WHERE N.SETID = 'SHARE'
AND N.TREE_NAME = 'ACCOUNTS'
AND N.EFFDT = '01-JAN-1901'
AND N.SETID = L.SETID AND N.SETCNTRLVALUE = L.SETCNTRLVALUE AND N.TREE_NAME = L.TREE_NAME AND N.EFFDT = L.EFFDT AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
ORDER BY 15, 14

↑ Back to top

Leave a Comment

Yay! You've decided to leave a comment. That's fantastic! Please keep in mind that comments are moderated and rel="nofollow" is in use. So, please do not use a spammy keyword or a domain as your name, or else it will be deleted. Let's have a personal and meaningful conversation instead. Thanks for dropping by! Your email address will not be published.