Home » SQL & PL/SQL » SQL & PL/SQL » Query to fill data from top records (Oracle SQL)
Query to fill data from top records [message #446006] Fri, 05 March 2010 02:24 Go to next message
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 Go to previous messageGo to next message
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 #446010 is a reply to message #446006] Fri, 05 March 2010 02:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Have a look at LAG/LEAD analytical functions.

regards,
Delna
Re: Query to fill data from top records [message #446011 is a reply to message #446008] Fri, 05 March 2010 02:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
./fa/1587/0/

regards,
Delna
Re: Query to fill data from top records [message #446013 is a reply to message #446006] Fri, 05 March 2010 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Also always post your Oracle version with 4 decimals, the solution JRowbottom gave may not work in your version and you will then ask for a solution for your version and then you have wasted JRowbottom's time.

Regards
Michel
Re: Query to fill data from top records [message #446040 is a reply to message #446006] Fri, 05 March 2010 05:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #446098 is a reply to message #446087] Fri, 05 March 2010 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It sounds like you're most likely right. Laughing

Regards
Michel
Re: Query to fill data from top records [message #446181 is a reply to message #446006] Fri, 05 March 2010 23:34 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
thank you very much.
I will figure out whether are there any original table (I think both should be use as a primary key of the original table.)

If I can't find it then I will export it and work around in Excel and import it back Very Happy
Re: Query to fill data from top records [message #446376 is a reply to message #446181] Mon, 08 March 2010 00:48 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: ORA-06503
Next Topic: create table usning bind variables in EXECUTE IMMEDIATE.
Goto Forum:
  


Current Time: Tue Sep 27 09:16:55 CDT 2016

Total time taken to generate the page: 0.07855 seconds