DB2 query for cursor

devangi83 · New User Joined: 05 May 2009 Posts: 7 Location: India

Hi,
I want to fetch some records from table in my program. I have declared cursor with below sql query

SELECT GRP-ID FROM TEST WHERE GRP-ID < 20
ORDER BY GRP-ID DESC
FETCH FIRST 5 ROWS ONLY

the above query is giving me output as follows

GRP-ID
19
18
17
16
15

Is there any way i can get the records in reverse order? i.e
15,16,17,18,19

if i am removing DESC, it is fetching GRP-ID 1,2,3,4,5 which i don't want, i want output to be 15,16,17,18,19

CICS Guy · Posted: Sat May 09, 2009 7:27 pm

So you actually want the last five rows?

devangi83 · New User Joined: 05 May 2009 Posts: 7 Location: India

yes.. and not in desc order..

CICS Guy · Posted: Sun May 10, 2009 8:23 pm

I'm not sure, but since there is a FETCH FIRST, isn't there a FETCH LAST?

devangi83 · New User Joined: 05 May 2009 Posts: 7 Location: India

thts for scrollable cursor, which i am not using...

CICS Guy · Posted: Sun May 10, 2009 8:53 pm

Where is "FETCH FIRST 5 ROWS ONLY" documented that it is not a "scrollable cursor"?
What is the diffefence between a "row" and a "rowset"?

Anuj Dhawan · Posted: Mon May 11, 2009 11:50 am

I'm not sure where this thread is heading to -- why not just change this

Srihari Gonugunta · Posted: Mon May 11, 2009 1:27 pm

Devan,

You can try the following query

SELECT A.GRP-ID FROM TEST A
WHERE 5 > (SELECT COUNT(*)
FROM TEST B
WHERE A.GRP-ID < B.GRP-ID
AND A.GRP-ID < 20) AND
A.GRP-ID < 20
ORDER BY A.GRP-ID;

Srihari Gonugunta · Posted: Mon May 11, 2009 2:27 pm

Just a small correction.

SELECT A.GRP-ID FROM TEST A
WHERE 5 > (SELECT COUNT(*)
FROM TEST B
WHERE A.GRP-ID < B.GRP-ID
AND B.GRP-ID < 20) AND
A.GRP-ID < 20
ORDER BY A.GRP-ID;

sushanth bobby · Posted: Mon May 11, 2009 3:14 pm

What version of DB2 are you using devangi ?

Sushanth

lineesh_kumar · New User Joined: 20 Feb 2006 Posts: 6

Select GRP-ID FROM
(SELECT GRP-ID FROM TEST WHERE GRP-ID < 20
ORDER BY GRP-ID DESC
FETCH FIRST 5 ROWS ONLY)
ORDER BY GRP-ID asc;

Try this...

Succor · Posted: Mon May 11, 2009 11:15 pm

lineesh_kumar, do you think this will pass the precompilation step?

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Devangi83,

Check out this earlier post ibmmainframes.com/viewtopic.php?t=15735&highlight=

Anuj Dhawan · Posted: Tue May 12, 2009 2:13 pm

dbzTHEdinosauer · Posted: Tue May 12, 2009 2:46 pm

devangi83 · New User Joined: 05 May 2009 Posts: 7 Location: India

Thanks Srihari,
Your query works.. but i'm sure my DBA wont allow it..

thnx anyways....

Srihari Gonugunta · Posted: Tue May 12, 2009 4:16 pm

Devan,
Let me know your DBA's comments.

Anuj,
if i am removing DESC, it is fetching GRP-ID 1,2,3,4,5 which i don't want, i want output to be 15,16,17,18,19.

This is from Devan's first post. I think this what you are missing.

dick scherrer · Posted: Tue May 12, 2009 8:10 pm

Hi Dick,

Anuj D. wrote:

dbzTHEdinosauer · Posted: Tue May 12, 2009 8:16 pm

Thx Dick,