1.1 - 8.10 - Cycles in NTree - 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)

A cycle is unidirectional; that is, the output can be different for Direction('up') and Direction('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 id,
  CAST (path1 AS VARCHAR(100)) AS path1,
  CAST (path2 AS VARCHAR(100)) AS path2,
  CAST (is_cycle AS VARCHAR(10)) AS is_cycle
  FROM nTree@coprocessor (
    ON emp_table_dept PARTITION BY department ORDER BY order_column
    USING
    RootNode (mgr_id = 'none')
    ParentID (mgr_id)
    NodeID (id)
    StartsWith (id = '11')
    Direction ('up')
    OutputType ('end')
    Results (path(name) AS path1, path(id) AS path2, IS_CYCLE )
    AllowCycles ('true') 
  ) AS dt ORDER BY id;
id path1                                    path2              is_cycle 
 -- ---------------------------------------- ------------------ -------- 
 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      
 14 Sarah->Elizabeth->Jessica                11->15->14         1

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

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