That is, CURRVAL and NEXTVAL are not valid PRIOR expressions. However, the PRIOR expression cannot refer to a sequence. In this case Oracle detects the loop at run time and returns an error.īoth the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. PRIOR is most commonly used when comparing column values with the equality operator. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. ĬONNECT BY PRIOR employee_id = manager_id and For example:ĬONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id.
If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop. The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. START WITH specifies the root row(s) of the hierarchy.ĬONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:ĭescription of the illustration hierarchical_query_clause.gif
0 kommentar(er)
