CODEWITHSUNDEEP
sqloracle-databasetreecommon-table-expressionrecursive-query
FIFAmnesia
FIFAmnesia

Reputation: 15

Recursive query in Oracle until a parent in the hierarchy meets a condition?

I have a table like the following:

ID  PARENT_ID  VALUE_ID
1   NULL       100
2   1          NULL
3   2          200
4   3          NULL 
5   1          300
6   2          NULL
7   6          400
8   7          500

And I'd like to be able to fetch each ID with its corresponding VALUE_ID. I want to do it in such a way that if a row has a VALUE_ID as NULL, it "inherits" the VALUE_ID of the first parent above it in the hierarchy, that has a VALUE_ID as NOT NULL. So that would be the query result:

ID  VALUE_ID
1   100
2   100      // -> inherits the value from PARENT_ID = 1;  
3   200
4   200      // -> inherits the value from PARENT_ID = 3;
5   300
6   100      // -> inherits the value from ID = 1, because the PARENT_ID = 2 also has VALUE_ID as NULL, so it goes deeper in the hierarchy;
7   400    
8   500

Could such a thing be accomplished with only one recursive or hierarchical query? Or be accomplished in general without a procedure, perhaps? With CTE, or CONNECT BY clause?

Upvotes: 1

Views: 4124

Answers (2)

MT0
MT0

Reputation: 167982

You can use a correlated hierarchical query and CONNECT_BY_ISLEAF to only return a single row:

SELECT id,
       parent_id,
       ( SELECT value_id
         FROM   table_name r
         WHERE  connect_by_isleaf = 1
         START WITH r.id = t.id
         CONNECT BY PRIOR parent_id = id
         AND PRIOR value_id IS NULL
       ) AS value_id
FROM   table_name t

So, for your test data:

CREATE TABLE table_name ( ID, PARENT_ID, VALUE_ID ) AS
SELECT 1, NULL, 100  FROM DUAL UNION ALL
SELECT 2, 1,    NULL FROM DUAL UNION ALL
SELECT 3, 2,    200  FROM DUAL UNION ALL
SELECT 4, 3,    NULL FROM DUAL UNION ALL
SELECT 5, 1,    300  FROM DUAL UNION ALL
SELECT 6, 2,    NULL FROM DUAL UNION ALL
SELECT 7, 6,    400  FROM DUAL UNION ALL
SELECT 8, 7,    500  FROM DUAL

This outputs:

ID | PARENT_ID | VALUE_ID
-: | --------: | -------:
 1 |      null |      100
 2 |         1 |      100
 3 |         2 |      200
 4 |         3 |      200
 5 |         1 |      300
 6 |         2 |      100
 7 |         6 |      400
 8 |         7 |      500

db<>fiddle here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a recursive CTE for this:

with cte(id, value_id, parent_value_id) as (
      select id, value_id, value_id as parent_value_id
      from t
      where value_id is not null
      union all
      select t.id, t.value_id, cte.parent_value_id
      from cte join
           t
           on t.parent_id = cte.id
      where t.value_id is null
     )
select *
from cte
order by id;

Here is a db<>fiddle.

Upvotes: 2

Related Questions