Query to fill data from top records [message #446006] |
Fri, 05 March 2010 02:24  |
Smith_X
Messages: 56 Registered: January 2007
|
Member |
|
|
Hello,
I want to fill data from the above records in the same column. How to do that by use SQL command please??
from
Invoice_No. Line_Item
00001 1
2
3
00002 1
2
00003 1
00004 1
2
00005 1
to
Invoice_No. Line_Item
00001 1
00001 2
00001 3
00002 1
00002 2
00003 1
00004 1
00004 2
00005 1
|
|
|
Re: Query to fill data from top records [message #446008 is a reply to message #446006] |
Fri, 05 March 2010 02:34   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Without an additional column to order your result set by, you can't (or at least I can't).
With an order column, it's easy:create table test_147 (inv_no varchar2(10), line_item number,ord number);
insert into test_147 values('00001', 1,1);
insert into test_147 values('', 2,2);
insert into test_147 values('', 3,3);
insert into test_147 values('00002' ,1,4);
insert into test_147 values('', 2,5);
insert into test_147 values('00003', 1,6);
insert into test_147 values('00004', 1,7);
insert into test_147 values('', 2,8);
insert into test_147 values('00005', 1,9);
select inv_no,line_item,last_value(inv_no ignore nulls) over (order by ord)
from test_147;
|
|
|
|
|
|
Re: Query to fill data from top records [message #446040 is a reply to message #446006] |
Fri, 05 March 2010 05:32   |
Smith_X
Messages: 56 Registered: January 2007
|
Member |
|
|
Data
create table invoice (Invoice_No varchar2(10), Line_item number);
insert into invoice values('00001', 1);
insert into invoice values('', 2);
insert into invoice values('', 3);
insert into invoice values('00002' ,1);
insert into invoice values('', 2);
insert into invoice values('00003', 1);
insert into invoice values('00004', 1);
insert into invoice values('', 2);
insert into invoice values('00005', 1);
Result
create table invoice (Invoice_No varchar2(10), Line_item number);
insert into invoice values('00001', 1);
insert into invoice values('00001', 2);
insert into invoice values('00001', 3);
insert into invoice values('00002', 1);
insert into invoice values('00002', 2);
insert into invoice values('00003', 1);
insert into invoice values('00004', 1);
insert into invoice values('00004', 2);
insert into invoice values('00005', 1);
My Oracle database version is 10.2.0.4 please.
[Updated on: Fri, 05 March 2010 05:33] Report message to a moderator
|
|
|
Re: Query to fill data from top records [message #446052 is a reply to message #446006] |
Fri, 05 March 2010 06:00   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It can't be done with just the 2 columns as JRowbottom already pointed out. Rows in a table are stored in no specific order so there is no way to tell which rows without an invoice_no go with which rows that have an invoice_no.
|
|
|
Re: Query to fill data from top records [message #446087 is a reply to message #446040] |
Fri, 05 March 2010 07:10   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This looks like someone put a lot of effort in creating a report where duplicate master keys are left blank, and another person tries to revert the report back to complete data.
Sounds like you'd better use the original data instead.
|
|
|
|
|
Re: Query to fill data from top records [message #446376 is a reply to message #446181] |
Mon, 08 March 2010 00:48  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
In Excel, Access, Notepad or whatever, you will face the same problem as you do in Oracle: how do you know that the record you display as the second one in your example "belongs" to the first one?
It's not the tool that is flawed, it is the idea.
|
|
|