Tree Joins

PS/nVision 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 the Tree Manager, and is keyed by PROCESS_INSTANCE (a system-generated constant number for all the rows representing a particular tree) and tree node number. Because some database platforms only join tables efficiently if the field sizes match, the system uses up to 30 selector tables, one for each supported ChartField length. Each selector table has RANGE_FROM_nn and RANGE_TO_nn columns matching the corresponding ChartField size.

The following code is a typical SELECT 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 9AND A.ACCOUNT>=L.RANGE_FROM_06
 AND A.ACCOUNT<=L.RANGE_TO_06
 AND L.PROCESS_INSTANCE=198
 AND (L.TREE_NODE_NUM BETWEEN 16 AND 30
 OR L.TREE_NODE_NUM BETWEEN 35 AND 40)
GROUP BY TREE_NODE_NUM

The parts of this statement in boldface accomplish the tree criteria selection. 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 of the report.