Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jean-Marc.

Asked: April 30, 2007 - 9:37 am UTC

Last updated: March 28, 2018 - 10:09 am UTC

Version: 9.2

Viewed 1000+ times

You Asked

Recently in the Oracle magazine of nov-06 you explained how to fill up null values with the previous non-null value of a column.
I got a similar problem, but on hierachical query.
I need to fill up null columns from its parent non-null value instead of the previous row returned from the connect by.
The null value of children rows should inherit the value from its parent (or its grand parent if the parent is null, and so on).
I think analytic could make the job but I can't figure it out. Partitionnning data (analytic partition clause) by branch is not possible.

Thanks in advance

create table t1 (
   cod      varchar2(5),
   p_cod    varchar2(5),
   cola     varchar2(1)
 );

insert into t1 values ('TT000',null,null);
insert into t1 values ('TT691','TT000',null);
insert into t1 values ('TT155','TT691',null);
insert into t1 values ('TT122','TT691','A') ;
insert into t1 values ('TT113','TT122',null);
insert into t1 values ('TT194','TT000','A') ;
insert into t1 values ('TT393','TT194','B') ;
insert into t1 values ('TT791','TT393',null);
insert into t1 values ('TT153','TT791',null);
insert into t1 values ('TT391','TT393',null);
insert into t1 values ('TT14A','TT391','A') ;
insert into t1 values ('TT148','TT391',null);
insert into t1 values ('TT143','TT391',null);
insert into t1 values ('TT129','TT391','C') ;
insert into t1 values ('TT111','TT129',null);
insert into t1 values ('TT112','TT129',null);
insert into t1 values ('TT591','TT194','D');
insert into t1 values ('TT145','TT591',null);
insert into t1 values ('TT154','TT591',null);
insert into t1 values ('TT191','TT194',null);
insert into t1 values ('TT147','TT191',null);
insert into t1 values ('TT144','TT191',null);
insert into t1 values ('TT121','TT191','C') ;
insert into t1 values ('TT116','TT121',null);
insert into t1 values ('TT117','TT121',null);
insert into t1 values ('TT118','TT121','D') ;

The source data looks like:
SELECT LPAD(' ',3*(LEVEL-1)) || cod as cod, cola
FROM t1
START WITH cod='TT000'
CONNECT BY PRIOR cod=p_cod;

COD                  COLA
-------------------- ----
TT000                   
   TT691                
      TT155             
      TT122             A
         TT113          
   TT194                A
      TT393             B
         TT791          
            TT153       
         TT391          
            TT14A       A
            TT148       
            TT143       
            TT129       C
               TT111    
               TT112    
      TT591             D
         TT145          
         TT154          
      TT191             
         TT147          
         TT144          
         TT121          C
            TT116       
            TT117       
            TT118       D


And should be like the following once nulls are filled-up. (The root may be null)
COD                  COLA
-------------------- ----
TT000                   
   TT691                
      TT122             A
         TT113          A
      TT155             
   TT194                A
      TT393             B
         TT791          B
            TT153       B
         TT391          B
            TT14A       A
            TT148       B
            TT143       B
            TT129       C
               TT111    C
               TT112    C
      TT591             D
         TT145          D
         TT154          D
      TT191             A
         TT147          A
         TT144          A
         TT121          C
            TT116       C
            TT117       C
            TT118       D

and Tom said...

ops$tkyte%ORA9IR2> SELECT LPAD(' ',3*(LEVEL-1)) || cod as cod, cola,
  2         sys_connect_by_path( cola, '/' ) scb,
  3         substr( rtrim( sys_connect_by_path( cola, '/' ), '/' ),
  4                     instr(  rtrim( sys_connect_by_path( cola, '/' ), '/' ), '/', -1 )+1 ) scb
  5  FROM t1
  6  START WITH cod='TT000'
  7  CONNECT BY PRIOR cod=p_cod;

COD                  C SCB                  SCB
-------------------- - -------------------- --------------------
TT000                  /
   TT691               //
      TT155            ///
      TT122          A ///A                 A
         TT113         ///A/                A
   TT194             A //A                  A
      TT393          B //A/B                B
         TT791         //A/B/               B
            TT153      //A/B//              B
         TT391         //A/B/               B
            TT14A    A //A/B//A             A
            TT148      //A/B//              B
            TT143      //A/B//              B
            TT129    C //A/B//C             C
               TT111   //A/B//C/            C
               TT112   //A/B//C/            C
      TT591          D //A/D                D
         TT145         //A/D/               D
         TT154         //A/D/               D
      TT191            //A/                 A
         TT147         //A//                A
         TT144         //A//                A
         TT121       C //A//C               C
            TT116      //A//C/              C
            TT117      //A//C/              C
            TT118    D //A//C/D             D

26 rows selected.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jean-Marc Lessard, April 30, 2007 - 12:37 pm UTC

You'r right, sys_connect_by_path build the path and we just need to parse it! Analytics not necessary here.

By the way, your Ask Tom column in Oracle Magazine is always greate. I don't know if you can give us the thread title of the subjet you post (when possible), so it be helpful when we want to go further on a subject.

Thanks a lot (hopping you will come again in Montreal this year)

Useful tip for Oracle 12c

Jeffrey Kemp, March 28, 2018 - 2:36 am UTC

Just a note to say thanks; I used this solution in a query in 12c but I needed this for multiple columns in my query, so I used the new WITH FUNCTION feature of 12c to make it clearer, e.g.:

with
  function tail
    (p in varchar2
    ,d in varchar2 := '/')
    return varchar2 is
  begin
    return substr(rtrim(p,d), instr(rtrim(p,d),d,-1)+1);
  end tail;
SELECT LPAD(' ',3*(LEVEL-1)) || cod as cod, cola,
       sys_connect_by_path( cola, '/' ) scb,
       tail( sys_connect_by_path( cola, '/' ) ) scb
FROM t1
START WITH cod='TT000'
CONNECT BY PRIOR cod=p_cod;

Chris Saxon
March 28, 2018 - 10:09 am UTC

Thanks for sharing.