I can't understand what it adds to the result of the query. From the book that I'm learning:
If you prefix a column name with PRIOR in the select list (SELECT PRIOR EMPLOYEE_ID, ...), you specify the “prior” row’s value.
SELECT PRIOR EMPLOYEE_ID, MANAGER_ID, LPAD(' ', LEVEL * 2) || EMPLOYEES.JOB_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;
The only difference I see, is that it adds a NULL value in the first row and increments IDs of employees by 1.
PRIOR just takes a record from a previous record in the traversed hierarchy.
I think the best way to undestand how it works is to play with a simple hierarchy:
create table qwerty(
id int,
name varchar2(100),
parent_id int
);
insert all
into qwerty values( 1, 'Grandfather', null )
into qwerty values( 2, 'Father', 1 )
into qwerty values( 3, 'Son', 2 )
into qwerty values( 4, 'Grandson', 3 )
select 1234 from dual;
The below query traverses the above hierarchy:
select level, t.*
from qwerty t
start with name = 'Grandfather'
connect by prior id = parent_id
LEVEL ID NAME PARENT_ID
---------- ---------- -------------------- ----------
1 1 Grandfather
2 2 Father 1
3 3 Son 2
4 4 Grandson 3
If we add "PRIOR name" to the above query, then the name of "parent" is displayed. This vaue is taken from prevoius record in the hierarchy (from LEVEL-1)
select level, prior name as parent_name, t.*
from qwerty t
start with name = 'Grandfather'
connect by prior id = parent_id;
LEVEL PARENT_NAME ID NAME PARENT_ID
---------- -------------------- ---------- -------------------- ----------
1 1 Grandfather
2 Grandfather 2 Father 1
3 Father 3 Son 2
4 Son 4 Grandson 3