Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Retrieve the second 50,000 rows.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
aryanpa1

New User


Joined: 26 May 2007
Posts: 45
Location: Chennai

PostPosted: Wed Aug 29, 2007 1:23 pm    Post subject: Retrieve the second 50,000 rows.
Reply with quote

Hi ,
I have a big db2 table which contain 2 lakh rows.
First I need to retrieve first 50,000 rows.
Second I need to retrieve next 50,000 rows.
and so on.......

Can anybody suggest me queries for the above conditions.Thank you
Back to top
View user's profile Send private message

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Wed Aug 29, 2007 2:28 pm    Post subject:
Reply with quote

Hi Aryanpa1,


My Suggestion is unload first 1 Lakh rows, After that use a SORT step to skip the first 50000 records.



Thanks
Sai
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Aug 29, 2007 2:53 pm    Post subject: Reply to: Retrieve the second 50,000 rows.
Reply with quote

For selecting the first 50000 rows you can use like

Select * from tablename where condition fetch first 50000 rows only

for selecting the next 50000 rows

you should have the row number and giving a greater than condition
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Aug 29, 2007 5:21 pm    Post subject:
Reply with quote

There is no such thing as the first, second, or last 50000 rows in a database. The order can change on you from query to query without an order by clause. See previous posts by quite a few people.
Back to top
View user's profile Send private message
sanjayis01
Warnings : 1

New User


Joined: 13 Jun 2007
Posts: 55
Location: banaglore

PostPosted: Wed Aug 29, 2007 5:39 pm    Post subject: Reply to: Retrieve the second 50,000 rows.
Reply with quote

Hi

Try this this may work for you


select * from X where X <=10 or (X >=20 and X<=30) or (X>=40 and X<=50)
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Aug 29, 2007 6:04 pm    Post subject:
Reply with quote

You can't execute a where against the table name. You have from X and where X.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 29, 2007 7:20 pm    Post subject:
Reply with quote

Hello,

As has been pointed out, there is no such thing as the "first n rows" in an active table.

The only way you can guarantee consistent results from data that may change "on the fly" (as most tables do) is to unload the table in some sequence and then do as also previously suggested and work with the unloaded qsam file. You can use/skip as many as you like.

Is there some business requirement for this, is it homework, or?
Back to top
View user's profile Send private message
aryanpa1

New User


Joined: 26 May 2007
Posts: 45
Location: Chennai

PostPosted: Thu Aug 30, 2007 10:25 am    Post subject:
Reply with quote

Thank you....every body....
It is just like homework dont have any business requirement and ur suggestions worked.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 30, 2007 6:21 pm    Post subject:
Reply with quote

You're welcome icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us