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