1.1 - 8.10 - NTree Example: Show Reporting Structure by Department - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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.

emp_table_by_dept
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
  RootNode (mgr_id = 'none')
  ParentID (mgr_id)
  NodeID (id)
  StartsWith ('ROOT') 
  Direction ('DOWN')
  OutputType ('ALL')
  Results (PATH(name) AS path, PATH(id) AS path2)
) AS dt ORDER BY id;

Output

The output table shows two department trees, whose roots are Dave and Peter.

 id path                       path2          
 -- -------------------------- -------------- 
  1 Dave                       1             
  2 Dave->Kim                  1->2          
  3 Dave->Donna                1->3          
  4 Dave->Rob                  1->4          
  5 Dave->Kim->Fran            1->2->5       
  6 Dave->Kim->Mark            1->2->6       
  7 Dave->Donna->Richard       1->3->7       
  8 Dave->Rob->Pat             1->4->8       
  9 Dave->Rob->Don             1->4->9       
 10 Peter                      10            
 11 Peter->Sarah               10->11        
 12 Peter->Dale                10->12        
 13 Peter->John                10->13        
 14 Peter->Dale->Jessy->Sophia 10->12->15->14
 15 Peter->Dale->Jessy         10->12->15    
 16 Peter->Dale->Gary          10->12->16    
 17 Peter->John->Elizabeth     10->13->17    
 18 Peter->John->Richard       10->13->18

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.