|
View previous topic :: View next topic
|
| Author |
Message |
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
| Thanks Dick..The info was very useful... |
|
| Back to top |
|
|
deepak.vl
New User
Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
