- Root_Node
- Specify the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
- Node_ID
- Specify the SQL expression whose value uniquely identifies a node in the input table (for example, order_id).A node can appear multiple times in the data set, with different parents.
- Parent_ID
- Specify the SQL expression whose value identifies the parent node.
- AllowCycles
- Specify whether trees can contain cycles. If not, a cycle in the data set causes the function to throw an exception. For information about cycles, see Cycles in NTree.
- Starts_With
- Specify the node from which to start tree traversal—'root', 'leaf', or a SQL expression that identifies a node.
- Mode
- Specify the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
- Output
- Specify when to output a tuple—at every node along the traversal path ('all') or only at the end of the traversal path ('end').
- MaxDistance
- [Optional] Specify the maximum tree depth.
- Logging
- [Optional] Specify whether the function prints log messages.
- Result
- Specify aggregate operations to perform during tree traversal, using this syntax:
operation (expression) [ ALIAS alias ]
- operation
- One of the following:
operation operation Action for Path from Starts_With Node to Last Traversed Node PATH Outputs value of expression for each node, separating values with '->'. SUM Computes value of expression for each node and outputs sum of these values. LEVEL Outputs number of hops. MAX Computes value of expression for each node and outputs highest of these values. MIN Computes value of expression for each node and outputs lowest of these values. IS_CYCLE Outputs cycle (if any). AVG Computes value of expression for each node and outputs average of these values. PROPAGATE Evaluates expression with value of Starts_With node and propagates result to every node. - expression
- SQL expression. Must be * if operation is LEVEL or IS_CYCLE.
- alias
- Name of the output table column that contains the result of the operation.The function ignores alias if it is the same as an input table column name.