| View previous topic :: View next topic |
| Author |
Message |
jim haire
Beginner
Joined: 30 Dec 2002
Posts: 140
Topics: 40
|
 Posted: Fri Apr 06, 2012 12:15 pm Post subject: Select from delete when deleting multiple rows |
 |
|
With version 9 of DB2 there is a new capability to do a SELECT statement to get the rows which are being deleted.
I have an application where I have multiple rows being deleted and want to return certain columns for all the rows that were deleted. I am doing this in COBOL. However, there are not any good examples of how this can be done online or in the programming manuals.
I have set up my host variables as arrays:
| Code: |
01 DELETED-ROWS.
05 DELETED-ROW-VARIABLES.
10 HOST-VARIABLE-1 PIC S9(4) COMP OCCURS 9999 TIMES.
10 HOST-VARIABLE-2 PIC X(10) OCCURS 9999 TIMES.
|
Since it is a single delete (deleting multiple rows), can I code the SELECT in this way:
| Code: |
SELECT INTO :host-variable-1, :host-variable-2 FROM OLD TABLE
(DELETE FROM TABLE WHERE HOST-VARIABLE-2 < CURRENT DATE)
|
or because I am returning multiple rows from the SELECT, do I have to define a cursor for the rows being returned?
| Code: |
DECLARE DEL_CURS CURSOR FOR
SELECT INTO :host-variable-1, :host-variable-2 FROM OLD TABLE
(DELETE FROM TABLE WHERE HOST-VARIABLE-2 < CURRENT DATE)
|
After this I did a Single OPEN, FETCH, and CLOSE of the cursor.
After testing both methods, I got exactly the same results. All the records meeting the criteria were deleted, but only the first positions in the array contained values.
(Examples: HOST-VARIABLE-1 (1), HOST-VARIABLE-2 (1) ).
Is it possible to do what I'm doing and get all the deleted values loaded into the array? What am I missing? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
jim haire
Beginner
Joined: 30 Dec 2002
Posts: 140
Topics: 40
|
| Posted: Fri Apr 06, 2012 1:21 pm Post subject: |
|
|
Thanks for pointing me to my problem
I didn't have the NEXT ROWSET and number of rows to fetch on my FETCH statement. 
I appreciate the help! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
