use of Prior in Hierarchical retrieval

[Mahtab Alam]

I know the way to use prior to traverse Top down and Bottom up in a Hierarchical traversal.

But i don`t actually understood it .

Case 1--When prior is used immediately after connect by

connect by prior column1=column2

Top down
column1=parent key
column2=child key

Bottom up
column1=child key
column2=parent key


Case 2--When prior is used after column1

connect by column1=prior column2

Top down
column1=child key
column2=parent key

Bottom up
column1=parent key
column2=child key

I want to get the feel that I actually understood how to use prior.

[Martin Vashko]

Did you have a look at the documentation?

Specifically,

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.

[Mahtab Alam]

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)


select level,employee_id,title,reports_to
from employee_chart
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.

[Martin Vashko]

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).