IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to fetch records in between.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Fetch data from programs execute (dat... DB2 3
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top