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
PRIOR
operator returns previous value in a hierarchy build using CONNECT BY
clause.
WITH hierarchy(id, parent_id, value) AS (
SELECT 1, NULL, 'root' FROM dual UNION ALL
SELECT 2, 1, 'child 1' FROM dual UNION ALL
SELECT 3, 1, 'child 2' FROM dual UNION ALL
SELECT 4, 3, 'grand child 1' FROM dual
)
SELECT
hierarchy.*, LEVEL depth, PRIOR value
FROM
hierarchy
START WITH
parent_id IS NULL
CONNECT BY
PRIOR id = parent_id
This simple query connects the rows from root to leafs. The PRIORVALUE
column returns value of VALUE
column of row's parent row (predecessor within the hierarchy), so 'grand child 1' parent is 'child 2' or 'child 1' parent is 'root'. 'root', the first row within the hierarchy (LEVEL = 1
) doesn't have any parent therefore PRIOR
returns NULL
.
If you connect the hierarchy in opposite direction, from a leaf to the root, the PRIOR
operator will return child row that was used to connect the row you're looking at.
The LEVEL column shows the depth of specific row within the hierarchy.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With