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
 

 

Problem with FOR UPDATE OF and ORDER BY clause in Cursor?

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

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Thu Apr 10, 2008 3:26 pm    Post subject: Problem with FOR UPDATE OF and ORDER BY clause in Cursor?
Reply with quote

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

dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Apr 10, 2008 3:42 pm    Post subject:
Reply with quote

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 10, 2008 3:49 pm    Post subject:
Reply with quote

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 10, 2008 3:51 pm    Post subject:
Reply with quote

sorry dbzTHEdinosauer .. i didnt see ur reply ...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Apr 10, 2008 4:16 pm    Post subject:
Reply with quote

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
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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am


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