IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

ORDER BY clause in DB2 Cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 1020
Location: India

PostPosted: Thu Dec 10, 2009 4:54 pm
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: 8797
Location: Welsh Wales

PostPosted: Thu Dec 10, 2009 5:01 pm
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
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
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: 78
Location: Mysore

PostPosted: Mon Dec 14, 2009 7:45 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Rotate partition-logical & physic... DB2 0
No new posts To search DB2 table based on Conditio... DB2 1
No new posts DB2 Load - Sort Or order BY DB2 1
No new posts GDG all in sequence order JCL & VSAM 9
No new posts Combining more 4 files with sorted or... DFSORT/ICETOOL 3
Search our Forums:

Back to Top