Argument | Category | Description |
---|---|---|
Root_Node | Required | Specifies the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL). |
Node_ID | Required | 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 | Required | Specifies the SQL expression whose value identifies the parent node. |
Allow_Cycles | Required | Specifies whether trees can contain cycles. If not, a cycle in the data set causes the function to throw an exception. The default value is 'false'. For information about cycles, refer to Cycles in nTree. |
Starts_With | Required | Specifies the node from which to start tree traversal—must be 'root', 'leaf', or a SQL expression that identifies a node. |
Mode | Required | Specifies the direction of tree traversal from the start node—up to the root node or down to the leaf nodes. |
Output | Required | Specifies when to output a tuple—at every node along the traversal path ('all') or only at the end of the traversal path ('end'). The default value is 'end'. |
Max_Distance | Optional | Specifies the maximum tree depth. The default value is 5. |
Logging | Optional | Specifies whether the function prints log messages. The default value is 'false'. |
Result | Required | 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, then expression must be *. alias is the name of the output table column that contains the result of the operation. The default value is the string operation(expression) (without the quotation marks); 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:
|