1.0 - 8.00 - NTree Example 1: Find Employee Reports - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

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.

employee_table
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
  Root_Node (mgr_id IS NULL)
  Node_ID (emp_id)
  Parent_ID (mgr_id)
  Starts_With (emp_id=100) 
  Mode ('down') 
  Output ('end')
  Result (PATH(emp_name) AS path)
) AS dt ORDER BY 1;

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