Home » SQL & PL/SQL » SQL & PL/SQL » Query for non nulls in a row
Query for non nulls in a row [message #448217] Sun, 21 March 2010 00:36 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi All,

I AM HAVING A TABLE WITH BELOW STRUCTURE AND VALUES.

Table A

Col1 Col2 Col3
M1 D1 V1
M1 D2
M1 D3 V2
M1 D4
M1 D5
M1 D6
M1 D7 V3
M1 D8
M2 D1 v4
M2 D2 v5
M2 D3
M2 D4 V6
M2 D5


Below is the scenario provided with sample data to replicate the actual requirement.

I am trying to write a query to get below output. But I am not able to succeed.

The column having null values should have the non null value as showed in example.

OUTPUT
------


Col1 Col2 Col3
M1 D1 V1
M1 D2 v1
M1 D3 V2
M1 D4 v2
M1 D5 v2
M1 D6 v2
M1 D7 V3
M1 D8 v3
M2 D1 v4
M2 D2 v5
M2 D3 v5
M2 D4 V6
M2 D5 v6

Below is the script for table and values


create table tmp (col1  varchar2(10),col2 varchar2(10),col3 varchar2(10));

insert into TMP (COL1, COL2, COL3)
values ('M1', 'D1', 'V1');
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D2', null);
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D3', 'V2');
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D4', null);
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D5', null);
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D6', null);
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D7', 'V3');
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D8', null);
insert into TMP (COL1, COL2, COL3)
values ('M2', 'D1', 'V4');
insert into TMP (COL1, COL2, COL3)
values ('M2', 'D2', 'V5');
insert into TMP (COL1, COL2, COL3)
values ('M2', 'D3', null);
insert into TMP (COL1, COL2, COL3)
values ('M2', 'D4', 'V6');
insert into TMP (COL1, COL2, COL3)
values ('M2', 'D5', null);
commit;






Any help really appreciated.

Thanks in advance



Re: Query for non nulls in a row [message #448219 is a reply to message #448217] Sun, 21 March 2010 01:20 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first define an order between your rows (and possibly a partitioning).
Assuming the order is COL1,COL2:
SQL> select col1, col2,
  2         nvl(col3, lower(last_value(col3 ignore nulls) over (order by col1,col2))) col3
  3  from tmp;
COL1       COL2       COL3
---------- ---------- ----------
M1         D1         V1
M1         D2         v1
M1         D3         V2
M1         D4         v2
M1         D5         v2
M1         D6         v2
M1         D7         V3
M1         D8         v3
M2         D1         V4
M2         D2         V5
M2         D3         v5
M2         D4         V6
M2         D5         v6

Please put your result charts between [code][/code] or [pre][/pre] tags to preserve column alignments.

Regards
Michel

[Updated on: Sun, 21 March 2010 01:24]

Report message to a moderator

Previous Topic: insert into temporary table
Next Topic: Problem with loop (yes, I searched the forum)
Goto Forum:
  


Current Time: Thu Sep 29 03:52:17 CDT 2016

Total time taken to generate the page: 0.11803 seconds