Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Error in using both ORDER BY and FOR UPDATE OF in a SQL

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

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Thu Aug 16, 2007 7:54 pm    Post subject: Error in using both ORDER BY and FOR UPDATE OF in a SQL
Reply with quote

Hi,
I tried the cursor .

Code:
DECLARE XYZ CURSOR WITH HOLD FOR             
         SELECT SL_ADJ_ENT_ID                                 
                 ,ACCT_ID                                       
               ,ENT_ID                                       
               ,SL_RQST_STAT_CD                               
               ,SL_SUB_STAT_CD                               
               ,SL_ADJ_RQST_AM                               
               ,SL_ADJ_TRANS_CT                               
               ,ACCT_EVNT_CD                                 
               ,SL_ADJ_CR_ACCT_ID                             
               ,SL_ADJ_DB_ACCT_ID                             
               ,SL_ADJ_SUS_ACCT_ID                           
               ,PER_SL_ADJ_END_DT                             
             
           FROM SJRT_SL_ADJ_RQST                             
          WHERE SL_RQST_STAT_CD    = :SJR-SL-RQST-STAT-CD     
            AND SL_SUB_STAT_CD     = :SJR-SL-SUB-STAT-CD     
            ORDER BY SL_ADJ_ENT_ID                                 
  FOR UPDATE OF SL_RQST_STAT_CD, SL_SUB_STAT_CD     


The precompiler threw a error message as
Quote:
"ORDER BY" CANNOT BE USED WITH A "FOR UPDATE OF" CLAUSE


Is it not possible to use a group by with an 'UPDATE FOR' clause? If no, is there any other way to order the rows fetched from a cursor for update in a particular way?
I am using DB2 Version 7
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 16, 2007 8:10 pm    Post subject:
Reply with quote

quoted from my favorite DB2 author Craig Mullins:

You cannot code the FOR UPDATE OF clause on cursors that access read-only data. These cursors contain SELECT statements that
  • Access read-only views
  • Join any tables
  • Issue subqueries for two or more tables
  • Access two or more tables using UNION
  • Use built-in functions
  • Use ORDER BY, GROUP BY, or HAVING
  • Specify DISTINCT
  • Specify literals or arithmetic expressions in the SELECT list
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Thu Aug 16, 2007 8:27 pm    Post subject: Reply to: Error in using both ORDER BY and FOR UPDATE OF in
Reply with quote

Thanks Dick..The info was very useful...
Back to top
View user's profile Send private message
deepak.vl

New User


Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

PostPosted: Fri Aug 24, 2007 6:24 pm    Post subject:
Reply with quote

But the cursor with ORDER BY clause doesn't necessary be a read-only. It should be updatable since what we are fetching is exactly a complete row from the table. What do u say?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Aug 24, 2007 6:36 pm    Post subject:
Reply with quote

deepak.vl,

I say you need to accept the definitions that IBM provides.

An ORDERED BY results table is read-only, per IBM. (Just like the speed limit is x, regardless of the traffic!)

Besides, you are not FETCHing complete rows from the base table,
you are FETCHing rows from the results table.

And to update a row, in an properly defined CURSOR FOR UPDATE,
you do not need to select all the columns in a row, only those that you plan to UPDATE and those that you need for your application processing.
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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts FUSE - IBM MQ - CICS integration error Kevin Vaz CICS 1 Mon Sep 18, 2017 4:31 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us