Traversing the Department Tree – PSTREENODE

In PeopleSoft, it is very common to come across requirements where the program gets a department ID as input and fetches data of that department and its sub-departments. In such scenarios we cannot escape from accessing the department security tree to pull the sub-departments of a department.

SQL Expert Vs SQR Smarty
When we speak of a problem there is always more than one problem solving technique. When I came through this type of requirement for the first time, I was shooting up my SQL skills. Hence I came up with a single select statement that will fetch the sub-departments from PSTREENODE table. Below is the select query that I used in my SQR program.

select t.tree_node from pstreenode t 
where t.setid = 'GBL'               
and t.tree_name = 'DEPT_SECURITY'
and t.tree_node_num >= (select t1.tree_node_num from pstreenode t1 
                                                     where t1.tree_node = <input dept>
                                                     and t1.effdt = t.effdt
                                                     and t1.setid = t.setid
                                                     and t1.setcntrlvalue = t.setcntrlvalue
                                                     and t1.tree_name = t.tree_name
                                                     and t1.tree_branch = t.tree_branch)
and t.tree_node_num_end <= (select t2.tree_node_num_end from pstreenode t2 
                                                       where t2.tree_node = <input dept>
                                                       and t2.effdt = t.effdt
                                                       and t2.setid = t.setid
                                                       and t2.setcntrlvalue = t.setcntrlvalue
                                                       and t2.tree_name = t.tree_name
                                                       and t2.tree_branch = t.tree_branch)
and t.effdt = (select max (t3.effdt) from pstreenode t3
                                                  where t3.effdt<=sysdate 
                                                  and t3.tree_node = t.tree_node
                                                  and t3.setid = t.setid
                                                  and t3.setcntrlvalue = t.setcntrlvalue
                                                  and t3.tree_name = t.tree_name
                                                  and t3.tree_branch = t.tree_branch
                                                  and t3.tree_node_num = t.tree_node_num) 

The SQL works perfectly well, but an execution plan might reveal that the "costs" are a bit heavy.

After a long time, when I encountered the same requirement – once again in an SQR program, I thought of implementing something different and probably better. And I came up with a couple of SQR procedures to accomplish the same effect.

Begin-Procedure Get-Departments
    Let $deptid = <input_dept_id>
    Let $leaf_node = ‘N'
    Let $DEPT_CLAUSE = '('''||$deptid||''')'   
    While $leaf_node <> 'Y'
            Let $dept_string = ''
            Let $leaf_node = 'Y' 
            Do Fetch-Subdept
            Let $DEPT_CLAUSE = '(''' || $dept_string || ''')'
End- Procedure
Begin-Procedure Fetch-Subdept
    Move &A.TREE_NODE to $treenode
    Let $leaf_node = 'N'
    If $dept_string <> ''
          Let $dept_string = $dept_string || ','
          Let $dept_string = $dept_string || '''' || $treenode  || ''''

The above method will traverse the department tree on Breadth-first Search. Performance-wise, the SQR procedures were a tad faster than the SQL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: