A cycle is unidirectional; that is, the output can be different for Direction('up') and Direction('down'). For example, consider this table:
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