CODEWITHSUNDEEP
sqloracle-databasehierarchical
smackenzie
smackenzie

Reputation: 3022

Difference caused by placement of PRIOR statement in an Oracle hierarchical query

Seems you can do both the following in Oracle when working with Connect By.

 CONNECT BY NOCYCLE 
    parent_id = PRIOR child_r_object_id

 CONNECT BY NOCYCLE PRIOR 
    parent_id = child_r_object_id

What is the difference, most examples on line tend to use the second syntax, but both execute.

Upvotes: 2

Views: 188

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

Basically you define to build the hierarchy from top-to-down or from down-to-top.

Have a look at this examples to see the difference:

WITH t(person, parent_id, ID) AS (
    SELECT 'Grandma', NULL, 1 FROM dual
    UNION ALL SELECT 'Mother', 1, 10 FROM dual
    UNION ALL SELECT 'Daughter', 10, 100 FROM dual
    UNION ALL SELECT 'Son', 10, 101 FROM dual)
SELECT person AS leaf_person, CONNECT_BY_ROOT(person) AS top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'down' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR ID
UNION ALL
SELECT person as leaf_person, CONNECT_BY_ROOT(person) as top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'up' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH ID IN (100,101)
CONNECT BY PRIOR parent_id = ID;


+-----------------------------------------------------------------+
|LEAF_PERSON|TOP_PERSON|SYS_CONNECT_BY_PATH(PERSON,'->')|DIRECTION|
+-----------------------------------------------------------------+
|Daughter   |Grandma   |->Grandma->Mother->Daughter     |down     |
|Son        |Grandma   |->Grandma->Mother->Son          |down     |
|Grandma    |Daughter  |->Daughter->Mother->Grandma     |up       |
|Grandma    |Son       |->Son->Mother->Grandma          |up       |
+-----------------------------------------------------------------+

Typically you have just one root (i.e. parent_id IS NULL) or at least determined root elements, thus most examples use the "top-to-down" direction.

Upvotes: 3

ekochergin
ekochergin

Reputation: 4129

The "prior" keyword what field is for parent record.

for example this

parent_id = PRIOR child_r_object_id

means "child_r_object_id from parent record is equal to parent_id of child line"

PRIOR parent_id = child_r_object_id

Means exactly the opposite - parent_id of parent record equals to child_r_object_id of a child record"

Upvotes: 0

Related Questions