CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
Ralf S
Ralf S

Reputation: 190

Latest values of parents in hierarchical SQL/PL query

Good Folks,

let's assume a tree like this:

    A (100)
    |
    +--B (50)
    |  |
    |  C (NULL)
    |  |
    |  E (NULL)
    |
    +--D (30)
       |
       B (20)

In this tree a letter shall represent a node's name and the number in parentheses some value. I know how to crawl through all nodes with a hierarchical query (using connect by etc.). However, I would like to retrieve the non-null value of the (grand-)parent node, if the current node's value is null. Thus, such a query should yield a table like the following:

NAME  PARENT  VALUE
A     NULL    100
B     A       50
B     D       20
C     B       50
D     A       30
E     C       50

Does anyone of you know how to achieve this?

Code so far:

SELECT NAME, PARENT, 
   CASE VALUE
   WHEN IS NULL THEN (SELECT VALUE FROM SOMETABLE WHERE NAME = PARENT) -- this returns more than one value
   ELSE VALUE
   END CASE AS VALUE
FROM SOMETABLE
START WITH NAME='A'
CONNECT BY NOCYCLE PRIOR NAME = PARENT

EDIT:

Instead of a table with parent references, you might as well consider a table with child references like this:

NAME  CHILD  VALUE
A     B      100
A     D      100
B     C      50
C     E      NULL
E     NULL   NULL
D     B      30
B     NULL   20

which should translate to:

NAME  CHILD  VALUE
A     B      100
A     D      100
B     C      50
C     E      50
E     NULL   50
D     B      30
B     NULL   20

Upvotes: 1

Views: 71

Answers (2)

kfinity
kfinity

Reputation: 9091

I'd recommend Ponder's CTE answer. If you must use CONNECT BY for some reason, Oracle doesn't offer many ways to access prior rows in the hierarchy - there's either PRIOR (which only goes 1 level back), or CONNECT_BY_ROOT (which only looks at the root node), or SYS_CONNECT_BY_PATH (which is cumbersome to use, since you need to resort to string manipulation). 

SELECT NAME, PARENT, 
   -- COALESCE(VALUE, PRIOR VALUE) AS VALUE, -- only works 1 level back
   regexp_substr(rtrim(SYS_CONNECT_BY_PATH(value, ','),','),'[^,]*$') as VALUE
FROM SOMETABLE
START WITH NAME='A'
CONNECT BY NOCYCLE PRIOR NAME = PARENT;

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

Problem with your data is that B has two parent nodes and also has child node, so this part of tree is replicated. Anyway you can achieve your goal easily with recursive CTE:

with c(name, parent, value) as (
    select name, parent, value from sometable where name = 'A' union all
    select t.name, t.parent, nvl(t.value, c.value) 
      from c join sometable t on c.name = t.parent)
select * from c

dbfiddle demo

Upvotes: 3

Related Questions