CONNECT_BY_ROOT operators are valid in only hierarchical queries.
In a hierarchical query, the
CONNECT BY condition must contain at least one expression that is qualified by the
PRIOR operator. The result of the
PRIOR operator is obtained by using the parent row of the current row to compute the expression that immediately follows the operator.
PRIOR is most commonly used in an equality comparison of columns.
PRIOR can be placed on either side of the comparison operator. Theoretically, operators other than the equal sign (=) can be used in
CONNECT BY clauses. However, the conditions that are created by these other operators may cause loops. In this case, ApsaraDB for OceanBase detects the loops at runtime and returns an error.
CONNECT_BY_ROOT is a unary operator and is valid in only hierarchical queries. The parameters of
CONNECT_BY_ROOT can be columns and most expressions. The result of the
CONNECT_BY_ROOT operator equals to the value that is obtained by using the data of the root row to calculate the parameter.
You cannot specify this operator in a
START WITH or
CONNECT BY condition.