|
View previous topic :: View next topic
|
| Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
I have declaraed my cursor like below,
| Code: |
EXEC SQL DECLARE CNTL_TABLE_CSR2 CURSOR WITH HOLD FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
* FOR UPDATE OF CNTL_FLAG,CNTL_DTE
ORDER BY CNTL_FLAG,CNTL_DTE
END-EXEC. |
Here i want to use FOR UPDATE OF clause and ORDER BY for my requirement purpose.but DB2 is not allowing me to use both in the declared cursor.
In my program i am updating the table columns(CNTL_DTE,CNTL_FLAG).if i commented the FOR UPDATE OF cluse,the UPDATE statement is updating all rows for CNTL_DTE,CNTL_FLAG columns. how to control the update in my program? |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Had you bothered to read the manual, you would know that you can not order an update cursor.
your question
| Quote: |
| In my program i am updating the table columns(CNTL_DTE,CNTL_FLAG).if i commented the FOR UPDATE OF cluse,the UPDATE statement is updating all rows for CNTL_DTE,CNTL_FLAG columns. how to control the update in my program? |
exhibits the same lack of knowledge.
you update after a fetch with the current cursor clause - when you want to update a row. otherwise, fetch until you have something to update.
15 minutes with the manual and you would not have had these questions and misconceptions. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| u cannot mention an order by clause in the declaration of a cursor that is to be used for UPDATE unless it is a static scrollable cursor ... if u require order by clause then why dont u go for a search update rather than a positioned update ... tht is instead of using the current cursor of clause for update fetch the value and then do a normal update ... |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| sorry dbzTHEdinosauer .. i didnt see ur reply ... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
no problem, ashimer, you provided an acceptable alternative.
I wonder why the cursor needs to be ordered? most often it does not. |
|
| Back to top |
|
|
|
|
