want to fill the null values with previous name ('Orange' and 'mangoes') after spending few time i have build a query for this same.
Scenario - 1
Mean while i also found one function which can ease your work with the help of "last_value".
feasibility version - 10g, 11g 12c
Scenario - 2
feasibility version - all versions of Oracle
Mean while i also found one function which can ease your work with the help of "last_value".
feasibility version - 10g, 11g 12c
Scenario - 2
feasibility version - all versions of Oracle
select id, substr(max(name_copy) over (order by id),8) name_copy , value, city from ( select id, value, city, case when name is not null then to_char(row_number() over (order by id),'fm0000000') || name end name_copy from test )
Create script for this post:
ReplyDeletedrop table test;
create table test ( id number ,name varchar2(30), value number, city varchar2(30));
insert into test values ( 1, 'Orange', 40, 'DELHI');
insert into test values ( 2, null, 50, 'MUMBAI');
insert into test values ( 3, null, 50, 'CHENNAI');
insert into test values ( 4, null, 30, 'BIHAR');
insert into test values ( 5, 'Mango', 60, 'DELHI');
insert into test values ( 6, null, 65, 'MUMBAI');
insert into test values ( 7, null, 58, 'CHENNAI');
insert into test values ( 8, null, 50, 'BIHAR');
commit;