Retrieve nth Row from a table without a condition clause

[DSJW]

Hi Forum Members

I am using embedded SQL within a COBOL program and I want to retrieve a row from a table without using condition statements (e.g. where a = b) or a cursor.

For example, if the table had 100 rows, and I want to retrieve the 54th is this possible. The 54th row would actually be the nth row specified by a variable.

If you can help send us an email

Many thanks
davesjwilliams@yahoo.co.uk

 

[MarcLodge]

Hi Dave,
I'm not sure about this, but within ANSI SQL there exists a function called ROWCOUNT which is what you want. Unfortunately, I don't think it is available in DB2. I know that there are some OLAP functions which possible do a similar thing, that might be of use, see here:

http://www-3.ibm.com/cgi-bin/db2www...d2w/report?fn=db2v7s0olapfunc.htm#Footnote_37

for some further info.

Sorry I can't be of more help. The only alternative is to

PERFORM XA-FETCH
VARYING WS-COUNT FROM 1 BY 1
UNTIL WS-COUNT > variable number required

which will do what you ant, but is a little cumbersome.

Marc

 

[gregsimpson]

DSJW,

apart from anything else unless you order by in your predicates you are not guaranteed that DB2 will always return the data in the same order. I think perhaps you need to give that some consideration.

Can you point out your business reason(by means of a populated table perhaps) for needing the nth row and perhaps we can recommend something for you.

Cheers
Greg