View previous topic :: View next topic
|
Author |
Message |
CHANDAN KAPOOR
New User
Joined: 12 Apr 2008 Posts: 36 Location: NOIDA
|
|
|
|
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 |
|
|
anandinmainframe
Active User
Joined: 31 May 2007 Posts: 171 Location: India
|
|
|
|
Chandan,
Can you please explain in words what you are trying to do, so that someone will help you on this. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
Why do you need an ORDER BY to accomplish your updating? |
|
Back to top |
|
|
rexx77
New User
Joined: 14 Apr 2008 Posts: 78 Location: Mysore
|
|
|
|
That is the same doubt i have in my mind, why do you want to sort the records before updating it? |
|
Back to top |
|
|
|