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
 

 

DB2 query for cursor

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

New User


Joined: 05 May 2009
Posts: 7
Location: India

PostPosted: Sat May 09, 2009 7:11 pm    Post subject: DB2 query for cursor
Reply with quote

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
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sat May 09, 2009 7:27 pm    Post subject:
Reply with quote

So you actually want the last five rows?
Back to top
View user's profile Send private message
devangi83

New User


Joined: 05 May 2009
Posts: 7
Location: India

PostPosted: Sun May 10, 2009 6:21 pm    Post subject:
Reply with quote

yes.. and not in desc order..
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun May 10, 2009 8:23 pm    Post subject:
Reply with quote

I'm not sure, but since there is a FETCH FIRST, isn't there a FETCH LAST?
Back to top
View user's profile Send private message
devangi83

New User


Joined: 05 May 2009
Posts: 7
Location: India

PostPosted: Sun May 10, 2009 8:28 pm    Post subject: Reply to: DB2 query for cursor
Reply with quote

thts for scrollable cursor, which i am not using...
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun May 10, 2009 8:53 pm    Post subject:
Reply with quote

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"?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon May 11, 2009 11:50 am    Post subject:
Reply with quote

I'm not sure where this thread is heading to -- why not just change this
Code:
ORDER BY GRP-ID DESC
to
Code:
ORDER BY GRP-ID ASC
in your query. . . icon_confused.gif
This will not meet the requirment.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon May 11, 2009 1:27 pm    Post subject:
Reply with quote

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;
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon May 11, 2009 2:27 pm    Post subject:
Reply with quote

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;
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon May 11, 2009 3:14 pm    Post subject:
Reply with quote

What version of DB2 are you using devangi ?

Sushanth
Back to top
View user's profile Send private message
lineesh_kumar

New User


Joined: 20 Feb 2006
Posts: 6

PostPosted: Mon May 11, 2009 10:18 pm    Post subject: Reply to: DB2 query for cursor
Reply with quote

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...
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Mon May 11, 2009 11:15 pm    Post subject: Reply to: DB2 query for cursor
Reply with quote

lineesh_kumar, do you think this will pass the precompilation step?
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue May 12, 2009 3:26 am    Post subject:
Reply with quote

Devangi83,

Check out this earlier post http://ibmmainframes.com/viewtopic.php?t=15735&highlight=
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue May 12, 2009 2:13 pm    Post subject:
Reply with quote

Quote:
This will not meet the requirment.
I'm surely missing something. Please assist.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 12, 2009 2:46 pm    Post subject:
Reply with quote

Anuj D. wrote:
Quote:
This will not meet the requirment.
I'm surely missing something. Please assist.


I am missing something; from where is the first quote?

not being a butt-head, but occasionally I see a post and then it is gone.
Back to top
View user's profile Send private message
devangi83

New User


Joined: 05 May 2009
Posts: 7
Location: India

PostPosted: Tue May 12, 2009 4:00 pm    Post subject:
Reply with quote

Thanks Srihari,
Your query works.. but i'm sure my DBA wont allow it.. icon_cry.gif thnx anyways.... icon_biggrin.gif
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue May 12, 2009 4:16 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 12, 2009 8:10 pm    Post subject:
Reply with quote

Hi Dick,

Anuj D. wrote:

Quote:
Quote:
This will not meet the requirment.

I'm surely missing something. Please assist.



Quote:
I am missing something; from where is the first quote?
The quote is in the "fine print" of the first reply from Anuj.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 12, 2009 8:16 pm    Post subject:
Reply with quote

Thx Dick,
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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