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
 

 

ORDER BY clause in DB2 Cursor

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

New User


Joined: 12 Apr 2008
Posts: 36
Location: NOIDA

PostPosted: Thu Dec 10, 2009 10:22 am    Post subject: ORDER BY clause in DB2 Cursor
Reply with quote

Hi ,

I have declared the cursor like

Code:
DECLARE GETMSG_CURSOR CURSOR WITH HOLD FOR   
   SELECT A_T_TFR_MSG_CDE,                   
          A_T_TFR_MSG_TIMESM,                 
          A_T_TFR_MSG_TYP,                   
          A_T_TFR_MSG_ERR,                   
          A_T_TFR_MSG_TXT,                   
          A_T_TFR_MQ_MSG                     
   FROM TE.UTE02401_AMNHLDLOG                 
   WHERE A_T_TFR_MSG_CDE = 'S'               
   ORDER BY A_T_TFR_MSG_TIMESM,A_T_TFR_MSG_TYP
   FOR UPDATE OF A_T_TFR_MSG_CDE;

Code'd

but Im getting compilation error as "ORDER BY" CANNOT BE USED WITH A "FOR UPDATE OF" CLAUSE

Can anybody advice me that how can I used both orderby and update clause in Cursor.
Back to top
View user's profile Send private message

anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Thu Dec 10, 2009 2:17 pm    Post subject: Reply to: ORDER BY clause in DB2 Cursor
Reply with quote

Chandan,
Can you please explain in words what you are trying to do, so that someone will help you on this.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Dec 10, 2009 4:54 pm    Post subject:
Reply with quote

Chandan,

Check the SQL REFERENCE manual in ORDER BY section for this
Quote:
If the subselect is not enclosed within parentheses and is not the outermost fullselect, the ORDER BY clause cannot be specified. The ORDER BY clause cannot be used in an outermost fullselect that contains a FOR UPDATE clause.


Consider re-writing your SQL or wait for better suggestions.

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

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Dec 10, 2009 5:01 pm    Post subject:
Reply with quote

I can not for one second believe that CA have now purchased DB2.
Topic moved from CA products forum to DB2 forum.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Dec 10, 2009 7:00 pm    Post subject:
Reply with quote

as Sushanth said, you can not have an order by clause in an UPDATE cursor.

nor can you have a JOIN in an UPDATE cursor.

you can only have JOINs and ORDER BYs in readonly cursors.

which means that if you just really need the order by, it must be a read-only cursor and you will have to perform the update with a separate SQL statement. And, you will have to insure that the UPDATEs do not affect the result set of your cursor. read the manual; there are also threads in this forum that indicate how to update without affecting the result set of the cursor.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Fri Dec 11, 2009 11:22 am    Post subject:
Reply with quote

Why do you need an ORDER BY to accomplish your updating?
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 74
Location: Mysore

PostPosted: Mon Dec 14, 2009 7:45 pm    Post subject:
Reply with quote

That is the same doubt i have in my mind, why do you want to sort the records before updating it?
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 Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


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