|
View previous topic :: View next topic
|
| Author |
Message |
easebourne_ironfist
New User
Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad
|
|
|
|
Hi All ,
I am trying to use a subquery while declaring a updatable cursor to fetch for update a required column on a table .
I see the cursor is behaving as read only and is not updating what i am expecting . Just wanted to understand the logic behind this .
Thanks. |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
easebourne_ironfist,
Are you getting any error? Can you post here your DECLARE CURSOR and UPDATE statements? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Could you please show us what is that being tried?
Please paste the code within code tags |
|
| Back to top |
|
|
easebourne_ironfist
New User
Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad
|
|
|
|
I am running a easytrive program . The code is running fine .It does not gives any error . The thing is it wont update the db as we expect . To make things simple i gave time stamp value so that the cursor selects only one row . Code is as given below .
| Code: |
PARM ABEXIT SNAP DB2SSID(DB2T) 00010000
****************************** 00280000
FILE DUMMY 00290000
**************************** 00330000
* MM CURSOR HOST VARIABLES 00340000
**************************** 00350000
CLAIM-NUM W 7 P 0 00370000
*********************** 00500000
***** DB2 CURSORS ***** 00510000
*********************** 00520000
* 00530000
************************** 00560000
SQL DECLARE TEST CURSOR FOR + 00570000
SELECT A.CLM_NUM + 00580000
FROM GHTR.CLAIM AS A + 00600000
WHERE A.CLM_NUM = (SELECT B.CLM_NUM + 00610000
FROM GHTR.CLAIM_AUDIT AS B + 00620000
WHERE B.LAST_UPD_TP = '2012-07-07-22.59.17.955608' +00621000
) + 00622000
FOR UPDATE OF A.CLM_NUM + 00630000
QUERYNO 050 00640000
* 00650000
* 00820000
JOB INPUT NULL FINISH END-OF-JOB 00830000
* 00840000
DISPLAY ' ' 00850000
DISPLAY '************************************************' 00860000
DISPLAY '*** TEST RUN FOR CURSOR TESTING ***' 00870000
DISPLAY '************************************************' 00880000
DISPLAY ' ' 00890000
* 00900000
SQL OPEN TEST 01180000
* 01190000
IF SQLCODE NE 0 01200000
DISPLAY '-----------------' 01201000
DISPLAY ' ABEND IN OPEN' 01202000
DISPLAY '-----------------' 01210000
STOP EXECUTE 01270000
END-IF 01280000
* 01290000
* FETCH THE FIRST ROW 01300000
* 01310000
SQL FETCH TEST INTO + 01320000
:CLAIM-NUM 01330000
* 01350000
IF SQLCODE = 0 01360000
DISPLAY 'GOING FOR UPDATE' 01370000
SQL UPDATE GHTR.CLAIM + 01371000
SET CLM_NUM = 111111111111 + 01372000
WHERE CURRENT OF TEST 01373000
DISPLAY SQLCODE 01374000
IF SQLCODE = 0 01375000
DISPLAY ' FINE ' 01376000
ELSE 01377000
DISPLAY 'IN UPDATE WITH ' SQLCODE 01378000
END-IF 01379000
ELSE 01380000
IF SQLCODE = 100 01390000
DISPLAY 'INFORMATORY: ROW HAS NOT BEEN FOUND' 01420000
ELSE 01430000
DISPLAY '-----------------' 01431000
DISPLAY ' ABEND IN FETCH' 01432000
DISPLAY '-----------------' 01440000
DISPLAY '-----------------' 01480000
RETURN-CODE = 9 01490000
STOP EXECUTE 01500000
END-IF 01510000
END-IF 01520000
* 01530000
SQL CLOSE TEST 01540000
* 01550000
* 01560000
STOP EXECUTE 01570000
* 01580000
******************** 02660000
END-OF-JOB. PROC 02670000
******************** 02680000
* 02690000
DISPLAY '**********************************************' 02700000
DISPLAY '*** EXECUTION NORMALLY TERMINATED ***' 02710000
DISPLAY '**********************************************' 02720000
DISPLAY ' ' 02730000
END-PROC 02740000
* 02750000
|
|
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
And can you also show us the output of displays you got?
If you cursor fetches only one row and then you check for SQLCODE
Yourr logic should do something like this I will give the pseudocode implement in Eazytrieve
| Code: |
MOVE 'N' to flag
Open cursor
Fetch cursor at end set flag as J
Do until flag='J'
Update table
Fetch cursor at end set flag as J
End
Close cursor |
|
|
| Back to top |
|
|
Ranjithkumar
New User
Joined: 10 Sep 2008
Posts: 93
Location: India
|
|
|
|
If CLM_NUM is the primary key of table GHTR.CLAIM then UPDATE operation with WHERE CURRENT OF will not work.
Also your code is not designed to abend when the update statement SQLCODE is other than ZERO.If you could post the SYSOUT display, it will be easy to interpret. |
|
| Back to top |
|
|
easebourne_ironfist
New User
Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad
|
|
|
|
Hi All ,
Thanks for your efforts , but i got the answer to my question .
I guess the question was not properly understood . The job
that i am running wont return any error . Its will give max cc 0
and sql query also return normal return code only .
I mentioned that everything seems fine but the result is not coming as expected .The rows are not getting updated .
This happens becuase the cursor becomes ready only . It reads the
subquery and make the rest of the cursor read only making it
incapable of updating anythinng in the table . |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
| Thanks for your efforts , but i got the answer to my question . |
The Answer is?? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
I mentioned that everything seems fine but the result is not coming as expected .The rows are not getting updated .
This happens becuase the cursor becomes ready only . It reads the
subquery and make the rest of the cursor read only making it
incapable of updating anythinng in the table |
I believe this is the answer. |
|
| Back to top |
|
|
|
|
