Input
The input table, emp_table_by_dept, contains data to build trees of departments, and is partitioned by department. In each partition, each row represents one employee in the department, identifying the employee by identifier and name and his or her manager by identifier. Each employee with no manager becomes the root of the department tree. The other employees in that department become children of their managers.
department | id | name | mgr_id |
---|---|---|---|
Marketing | 1 | Dave | none |
Marketing | 2 | Kim | 1 |
Marketing | 3 | Donna | 1 |
Marketing | 4 | Rob | 1 |
Marketing | 5 | Fran | 2 |
Marketing | 6 | Mark | 2 |
Marketing | 7 | Richard | 3 |
Marketing | 8 | Pat | 4 |
Marketing | 9 | Don | 4 |
Engineering | 10 | Peter | none |
Engineering | 11 | Sarah | 10 |
Engineering | 12 | Dale | 10 |
Engineering | 13 | John | 10 |
Engineering | 14 | Sophia | 15 |
Engineering | 15 | Jessy | 12 |
Engineering | 16 | Gary | 12 |
Engineering | 17 | Elizabeth | 13 |
Engineering | 18 | Richard | 13 |
SQL Call
SELECT * FROM NTree@coprocessor ( ON emp_table_by_dept PARTITION BY department USING Root_Node (mgr_id = 'none') Parent_ID (mgr_id) Node_ID (id) Starts_With ('root') Mode ('down') Output ('all') Result (PATH(name) AS path, PATH(id) AS path2) ) AS dt ORDER BY path, path2;
Output
The output table shows two department trees, whose roots are Dave and Peter.
id | path | path2 |
---|---|---|
1 | Dave | 1 |
3 | Dave->Donna | 1->3 |
7 | Dave->Donna->Richard | 1->3->7 |
2 | Dave->Kim | 1->2 |
5 | Dave->Kim->Fran | 1->2->5 |
6 | Dave->Kim->Mark | 1->2->6 |
4 | Dave->Rob | 1->4 |
9 | Dave->Rob->Don | 1->4->9 |
8 | Dave->Rob->Pat | 1->4->8 |
10 | Peter | 10 |
12 | Peter->Dale | 10->12 |
16 | Peter->Dale->Gary | 10->12->16 |
15 | Peter->Dale->Jessy | 10->12->15 |
14 | Peter->Dale->Jessy->Sophia | 10->12->15->14 |
13 | Peter->John | 10->13 |
17 | Peter->John->Elizabeth | 10->13->17 |