Home » SQL & PL/SQL » SQL & PL/SQL » Splitting total records returned
Splitting total records returned [message #187927] Wed, 16 August 2006 06:54 Go to next message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
I have a script ,which returns 5000 records and i need to pass all these records via an interface. But the interface can transfer a maximum of 500 records per day.

So i need to split the total records into multiple of 500.

How effectively, the total records can be splitted ?Is there any in built function available or a script needed?

Can some one please help me??
Re: Splitting total records returned [message #187956 is a reply to message #187927] Wed, 16 August 2006 08:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
So what do you plan on doing with the other 4500 records? And doesn't this cause a backlog? You return 500 records on day 1 and then 4500 get pushed back to day 2. Then on day 2 you have 5000 more, process 500 and now you have 9000 backlogged records.

If all you seek is to use 500 records and don't care about the other 4500, just use
 and rownum <= 500 
Re: Splitting total records returned [message #188088 is a reply to message #187927] Thu, 17 August 2006 01:38 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS
Re: Splitting total records returned [message #188089 is a reply to message #188088] Thu, 17 August 2006 01:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gbarbisan wrote on Thu, 17 August 2006 08:38

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS


Credit goes to Tom Kyte Wink.

MHE
Re: Splitting total records returned [message #188091 is a reply to message #188089] Thu, 17 August 2006 01:52 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
OF COURSE! Just give me the time to find the original link.
Here we go:

Click here


Maaher wrote on Thu, 17 August 2006 08:47

gbarbisan wrote on Thu, 17 August 2006 08:38

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS


Credit goes to Tom Kyte Wink.

MHE

[Updated on: Thu, 17 August 2006 03:30]

Report message to a moderator

Previous Topic: tough query,help me
Next Topic: max 5
Goto Forum:
  


Current Time: Fri Dec 09 09:51:31 CST 2016

Total time taken to generate the page: 0.18437 seconds