1.0 - 8.00 - Cycles in NTree - 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)

A cycle is unidirectional; that is, the output can be different for Mode('up') and Mode('down'). For example, consider this table:

emp_table_dept
department order_column id name salary mgr_id
Marketing 1 7 Don 20000.00 2
Marketing 2 2 Pat 30000.00 3
Marketing 3 3 Donna 60000.00 6
Marketing 4 9 Kim 50000.00 5
Marketing 5 4 Fred 40000.00 4
Marketing 6 5 Mark 70000.00 7
Marketing 7 6 Rob 10000.00 1
Marketing 8 5 Mark 10000.00 1
Marketing 9 1 Dave 10000.00 none
Marketing 10 1 Dave 10000.00 9
Engineering 1 10 Peter 10000.00 12
Engineering 1 10 Peter 10000.00 none
Engineering 2 11 Sarah 20000.00 10
Engineering 3 12 Sophia 30000.00 10
Engineering 4 15 Elizabeth 40000.00 12
Engineering 5 16 Richard 50000.00 12
Engineering 6 18 Carter 60000.00 17
Engineering 7 11 Sarah 20000.00 15
Engineering 8 13 John 70000.00 11
Engineering 9 14 Jessica 80000.00 11
Engineering 10 14 Jessica 80000.00 12
Engineering 11 15 Elizabeth 40000.00 14
Engineering 12 16 Richard 50000.00 15
Engineering 13 17 Gary 90000.00 16
Engineering 14 16 Richard 50000.00 18

This query returns the following table:

SELECT * FROM NTree@coprocessor (
  ON emp_table_dept
  PARTITION BY department
  ORDER BY order_column
  Root_Node (mgr_id = 'none')
  Parent_ID (mgr_id)
  Node_ID (id)
  Starts_With (id = '11')
  "Mode" ('up')
  "Output" ('end')
  "Result" (PATH (name) AS path, path (id) AS path2, IS_CYCLE (*))
  AllowCycles ('true')
) ORDER BY path, path2;
id path path2 is_cycle
14 Sarah->Elizabeth->Jessica 11->15->14 11
10 Sarah->Elizabeth->Jessica->Sophia->Peter 11->15->14->12->10 12
10 Sarah->Elizabeth->Sophia->Peter 11->15->12->10 12
12 Sarah->Peter->Sophia 11->10->12 10

If you specify "Mode('down')" in the preceding query, it returns the following table.

id path path2 is_cycle
15 Sarah->Jessica->Elizabeth 11->14->15 11
18 Sarah->Jessica->Elizabeth->Richard->Gary->Carter 11->14->15->16->17->18 16
13 Sarah->John 11->13