- Root_Node
- Specifies the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
- Node_ID
- Specifies 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
- Specifies the SQL expression whose value identifies the parent node.
- Allow_Cycles
- Specifies whether trees can contain cycles. If not, a cycle in the data set causes the function to throw an exception. Default: 'false'. For information about cycles, see Cycles in nTree.
- Starts_With
- Specifies the node from which to start tree traversal—must be 'root', 'leaf', or a SQL expression that identifies a node.
- Mode
- Specifies the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
- Output
- Specifies when to output a tuple—at every node along the traversal path ('all') or only at the end of the traversal path ('end'). Default: 'end'.
- Max_Distance
- [Optional] Specifies the maximum tree depth. Default: 5.
- Logging
- [Optional] Specifies whether the function prints log messages. Default: 'false'.
- Result
- Specifies aggregate operations to perform during tree traversal. The function reports the result of each aggregate operation in the output table. The syntax of aggregate is:
operation (expression) [ ALIAS alias ]
operation is either PATH, SUM, LEVEL, MAX, MIN, IS_CYCLE, AVG, or PROPAGATE.
expression is a SQL expression. If operation is LEVEL or IS_CYCLE, expression must be *.
alias is the name of the output table column that contains the result of the operation. Default: The string operation(expression); for example, PATH(node_name).
The function ignores alias if it is the same as an input table column name.For the path from the Starts_With node to the last traversed node, the operations do the following:- PATH
Outputs the value of expression for each node, separating values with '->'.
- SUM
Computes the value of expression for each node and outputs the sum of these values.
- LEVEL
Outputs the number of hops.
- MAX
Computes the value of expression for each node and outputs the highest of these values.
- MIN
Computes the value of expression for each node and outputs the lowest of these values.
- IS_CYCLE
Outputs the cycle (if any).
- AVG
Computes the value of expression for each node and outputs the average of these values.
- PROPAGATE
Evaluates expression with the value of the Starts_With node and propagates the result to every node.
- PATH