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
