Input
The input table, employee_table, contains the data to build a tree of employees. Each row represents one employee, identifying both the employee and his or her manager by identifier and name. The employee with no manager, Don, becomes the root of the tree. The other employees become children of their managers.
emp_id | emp_name | mgr_id | mgr_name |
---|---|---|---|
100 | Don | NULL | NULL |
200 | Pat | 100 | Don |
300 | Donna | 100 | Don |
400 | Kim | 200 | Pat |
500 | Fred | 400 | Kim |
SQL Call
This call finds the employees who report to employee 100 (either directly or indirectly) by traversing the tree of employees from employee 100 downward.
SELECT * FROM NTree@coprocessor ( ON employee_table PARTITION BY 1 USING RootNode (mgr_id IS NULL) NodeID (emp_id) ParentID (mgr_id) Direction('Down') StartsWith(emp_id=100) OutputType('END') Results (PATH(emp_name) AS path) ) AS dt;
Output
The output table shows that employee 100, Don, has two direct reports, Donna and Pat, and two indirect reports, Kim (who reports to Pat) and Fred (who reports to Kim).
id path --- ------------------- 300 don->donna 500 don->pat->kim->fred
Download a zip file of all examples and a SQL script file that creates their input tables.