Tree Joins

PS/nVision often relates tree node criteria to data tables by joining the data table to a tree selector table. This selector table contains a row for every detail range defined for the tree in PeopleSoft Tree Manager and is keyed by SELECTOR_NUM (a system-generated constant number for all the rows representing a particular tree) and the tree node number. Because some database platforms join tables efficiently only if the field sizes match, we use up to 30 selector tables, one for each supported field length. Each selector table has RANGE_FROM_nn and RANGE_TO_nn columns matching the corresponding field size.

The following is a typical Select statement for selection via nodes on a single tree.

SELECT L.TREE_NODE_NUM, SUM(POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT06 L
WHERE A.LEDGER='ACTUALS'
  AND A.FISCAL_YEAR=1991
  AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 9
  AND A.ACCOUNT>=L.RANGE_FROM_06
  AND A.ACCOUNT<=L.RANGE_TO_06
  AND L.SELECTOR_NUM=198
  AND (L.TREE_NODE_NUM BETWEEN 1612345 AND 3098765
      OR L.TREE_NODE_NUM BETWEEN 3512345 AND 4098765)
  GROUP BY TREE_NODE_NUM

The bold part of this statement accomplishes the tree criteria selection. If the report had tree criteria for other fields, their selector tables would be added to the From list and similar Join criteria to the Where clause. The Group By clause returns an answer row for each node that has a detail range attached to it; these node numbers are used to post amounts from the answer set into the appropriate rows or columns of the report.