Getting parent data if child data is null in Oracle hierarchical table

Question

In Oracle 10g I have the following hierarchical table:

corp_id, parent_corp_id, col1, col2, col3

I want to flatten out the structure such that we get the first row's data where col1 OR col2 OR col3 is not null.

So for example, suppose I have:

corp_id = 1
parent_corp_id = null
col1 = 5
col2 = NULL
col3 = NULL

corp_id = 3
parent_corp_id = 1
col1 = NULL
col2 = NULL
col3 = NULL

the results of this query would give me:

corp_id, parent_corp_id, col1, col2, col3
3      , 1             , 5   , NULL, NULL

Another scenario: Suppose I put col2 = 6 where corp_id = 3

Well, then the result set should be:

corp_id, parent_corp_id, col1, col2, col3
3      , 1             , NULL, 6,    NULL

In other words, if the child has data in one of these three columns we grab it. Otherwise, we try the parent and so on and so forth. Shouldn't be more than 3 levels deep but it could have 3 levels to look into.

Pretty new to hierarchical queries, so pardon me if this is a rudimentary question.

Bohemian · Answer 1 · 2014-09-20T14:44:58.630

0

Use the coalesce() function, which returns the first non-null value in its list:

select
    c.corp_id,
    c.parent_corp_id,
    coalesce(c.col1, p.col1) col1,
    coalesce(c.col2, p.col2) col2,
    coalesce(c.col3, p.col3) col3
from mytable c
left join mytable p on p.corp_id = c.parent_corp_id

to get the "first row that has a not-null value", add:

where coalesce(c.col1, p.col1, c.col2, p.col2, c.col3, p.col3) is not null
and rownum = 1

edited Sep 20 '14 at 14:44

answered Sep 20 '14 at 14:38

Bohemian

412,405
93
575
722

That won't work for me I'm afraid, I need a hierarchical query since I don't know how many levels I'll have to go up until I find a value... – mlnyc Sep 20 '14 at 14:39
@mlnyc is there an upper limit on the levels? – Bohemian Sep 20 '14 at 15:53

score 0 · Answer 2 · answered Sep 20 '14 at 15:49

You do need to use a hiearchial query (w/ the connect by clause) because of the fact that you have a parent with a child and that child is the parent of another child (although your example data doesn't bring that into play) however the requirement that you show the 'first not null col1' and the 'first not null col2' etc. is a separate issue from the hierarchical relationship altogether.

Try the following, I added some additional sample data to the fiddle (check the DDL on the left side) for illustrative purposes.

It looks like in your expected output you don't want to show the highest level parents, which is why I put "where s.parent_corp_id is not null" at the end. If you actually do want to show those, take that line out.

Otherwise, this will show the col1/col2/col3 values based on their group. Notice how in the example 2 is a high level parent and has 4 as a child, and 4 is also a parent of 8. So corp_id 8 and 4 are part of the same branch and they therefore show the same col1/col2/col3 values, and those are, based on your requirements, the first not null value of each throughout the branch.

Fiddle: http://sqlfiddle.com/#!4/ef218/14/0

with sub as
 (select corp_id,
         parent_corp_id,
         col1,
         col2,
         col3,
         level as lvl,
         rownum - level as grp
    from tbl
  connect by prior corp_id = parent_corp_id
   start with parent_corp_id is null),
col1_lvl as
 (select grp, col1
    from sub s
   where s.lvl = (select min(x.lvl)
                    from sub x
                   where x.col1 is not null
                     and x.grp = s.grp)),
col2_lvl as
 (select grp, col2
    from sub s
   where s.lvl = (select min(x.lvl)
                    from sub x
                   where x.col2 is not null
                     and x.grp = s.grp)),
col3_lvl as
 (select grp, col3
    from sub s
   where s.lvl = (select min(x.lvl)
                    from sub x
                   where x.col3 is not null
                     and x.grp = s.grp))
select s.corp_id, s.parent_corp_id, c1.col1, c2.col2, c3.col3
  from sub s
  left join col1_lvl c1
    on s.grp = c1.grp
  left join col2_lvl c2
    on s.grp = c2.grp
  left join col3_lvl c3
    on s.grp = c3.grp
 where s.parent_corp_id is not null

If this doesn't provide the output you're expecting based on the sample data I used please provide the expected output for the data I used in the DDL on the fiddle.

Getting parent data if child data is null in Oracle hierarchical table

2 Answers2