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
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.