Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to fetch records in between.

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

New User


Joined: 14 Nov 2005
Posts: 5

PostPosted: Wed May 31, 2006 5:32 pm    Post subject: How to fetch records in between.
Reply with quote

hi,
please let me know how to fetch records in between. ie from 11th record to 15 th record in a table without using cursor.
Back to top
View user's profile Send private message

rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Thu Jun 01, 2006 2:18 pm    Post subject:
Reply with quote

If you want to retreive/fetch using single query then you should use cursor concept.............

regards
jai
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jun 01, 2006 2:19 pm    Post subject:
Reply with quote

Hi,
I think, if your select query returns more than one row, then you must use a cursor.


Let me know if I am not correct here.

Thanks,
Parikshit.
Back to top
View user's profile Send private message
jeyaraj

New User


Joined: 14 Nov 2005
Posts: 5

PostPosted: Fri Jun 02, 2006 2:00 pm    Post subject: display records from 10th record to 15th record
Reply with quote

hi,
i want a query to display 10th record to 15th record. is there any possibility like display records using its record counter in DB2.
Back to top
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Fri Jun 02, 2006 4:56 pm    Post subject: Re: How to fetch records in between.
Reply with quote

Hi ,
You can do it using key value , say I have a field called Recseq (Record Sequence )

select * from tablea where keyfield in (select keyfield from tablea where keyfield>10) fetch first 5 rows only

or, if you have any sequence / identity or Rowid column , then you can try querying with that field

Let me know , if more questions

Thanks
Mahesh
Back to top
View user's profile Send private message
Jerry

New User


Joined: 16 Sep 2005
Posts: 42

PostPosted: Mon Jun 05, 2006 3:19 pm    Post subject:
Reply with quote

Hi Jeyaraj,

Hope this helps:

Select * from tbname fetch first 10 rows only where keyfield not in (select * from tbname fetch 9 rows only);

Select * from tbname fetch first 15 rows only where keyfield not in (select * from tbname fetch 14 rows only);

Correct me if I am wrong.

Regards,
Jerry.
Back to top
View user's profile Send private message
sri.mainframes

New User


Joined: 16 Feb 2006
Posts: 29
Location: MUMBAI

PostPosted: Mon Jun 05, 2006 6:33 pm    Post subject:
Reply with quote

hi Jerry ,

your query retrieves 10th and 15 th row only but not 10 to 15 rows

hi jeyaraj try this one

Select * from tbname fetch first 15 rows only where keyfield not in (select * from tbname fetch 9 rows only);

suggestions welcomed

regards
PRINCE
Back to top
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Jun 07, 2006 11:31 am    Post subject:
Reply with quote

You cant say that a row occurs on possition 10th and 15th until you are browsing data based on some order by clause.

In order to solve your problem look for primary keys of those rows.

and then use :

SELECT * FROM TABLEA WHERE PKEY IN (KEY11,KEY11,KEY12,KEY13,KEY14,KEY15);
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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us