This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
Oracle wrote:PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (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. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
The only caveat here is that the "parent row" and "child row" don't refer to any referential integrity constraint (after all, you can build hierarchical queries over tables that do not have any referential integrity constraint at all), but instead refer to the hierarchy of rows build by the query itself. In more complicated queries, it might be hard to imagine how the hierarchy of rows looks like just by looking at the query - at least it is for me, so I usually shuffle the PRIOR around a few times until the results look about right.
I am pasting a line from Oracle`s take on Hierarchical queries
"In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row."
I have steve O Hearn `s Sql expert book
and I can`t get this query
//this is the table
create table employee_chart(employee_id number primary key,title varchar2(50),reports_to number)
start with employee_id=1
connect by reports_to=prior employee_id
Now how I think about this query is
as employee_id is parent and we are starting with employee_id 1
Query will locate the row that have it`s reports_to value equal to employee_id 1
Then query will locate the rows that have it`s reports_to value equal to employee_id 2
and so on like this
My question is why then it shows the root entry because for it there is no reports_to value.
Mahtab Alam wrote:My question is why then it shows the root entry because for it there is no reports_to value.
There are two conditions specified in hierarchical query: the START WITH clause and the CONNECT BY clause. The START WITH specifies which rows from the query will form the roots of the hierarchy. They are included by definition, the CONNECT BY condition is not applied to them. The CONNECT BY only specifies how the hierarchy is build up, but not the roots (which is where the hierarchy starts).