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 get the 5th record ?

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

New User


Joined: 05 Apr 2005
Posts: 9
Location: india

PostPosted: Wed Jul 20, 2005 12:45 pm    Post subject: How to get the 5th record ?
Reply with quote

How to get the 5th record from a DB2 table or a 100th record from a vsam file directly without knowing any key of that particular record?
Thanks
Back to top
View user's profile Send private message

Abirami.YN

New User


Joined: 16 Jul 2005
Posts: 15
Location: India - Chennai

PostPosted: Wed Jul 20, 2005 7:25 pm    Post subject:
Reply with quote

Hi,

Try the following in DB2

SELECT *
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY empno ASC) AS rownumber,
empno, firstnme
FROM edwarde.employee
) AS foo
WHERE rownumber = 5;

After executing this query , tell me ur result (I am not sure)
Back to top
View user's profile Send private message
shobam

New User


Joined: 18 Jul 2005
Posts: 34
Location: CN

PostPosted: Fri Jul 22, 2005 12:33 pm    Post subject:
Reply with quote

You can find the Nth record or a range of records from a table using the ROW_NUMBER as abirami mentioned.

To know about ROW_NUMBER go thru

http://www.cis.unisa.edu.au/oracle/server.101/b10759/functions122.htm

Good Example is available in

http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx
Back to top
View user's profile Send private message
Gops

New User


Joined: 21 Jul 2005
Posts: 23

PostPosted: Fri Jul 29, 2005 5:06 pm    Post subject:
Reply with quote

Hi Guys,

Tried with both of your examples,
but I am getting a syntax error on the over clause of the query.

Cheers,
Gopal
Back to top
View user's profile Send private message
vasudev9

New User


Joined: 02 Aug 2005
Posts: 1
Location: india

PostPosted: Tue Aug 02, 2005 8:24 pm    Post subject:
Reply with quote

How to get the 5th record from a DB2 table or a 100th record from a vsam file directly without knowing any key of that particular record?

Hi

To get 100th record from a vsam file, try this in IDCAMS

REPRO -
INFILE(DD1) -
OUTFILE(DD2) -
SKIP(99) -
COUNT(1)

This example SKIPS 99 Records and copies next 1 record.

Hope this do..

vasudev
Back to top
View user's profile Send private message
withnams

New User


Joined: 06 Jul 2005
Posts: 26
Location: Chennai

PostPosted: Wed Aug 03, 2005 2:10 pm    Post subject: Re: How to get the 5th record ?
Reply with quote

It depends upon the version of the DB2 you are using?
Back to top
View user's profile Send private message
vijayamadhuri

Active User


Joined: 06 Apr 2005
Posts: 180

PostPosted: Fri Aug 05, 2005 3:20 am    Post subject: HI
Reply with quote

to obtain the 5 th record use the following

EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

To fetch the fifth row from the end of the result table, use this FETCH statement:
EXEC SQL FETCH ABSOLUTE -5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

In this way u can fetch any row u require
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


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