How would I trap the insert failure?
I appreciate you coming back to trying to help me with my basic grasping of error handling in SPL instead of only going with the single statement to do all processing.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 6:50 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.
But my response was not what you were asking for. I believe the condition handler you wrote is only effected by the fetch statement. The insert is thronging something else.
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 4:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.
Rob, If you want to skip the main table data then INNER JOIN the yetanothertable and you omit inserting when not match in yetanothertable.
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, February 28, 2019 4:36 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SPL question about error handling.
Rob,
Why do you need a cursor for this?
instead of
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from YetAnotherTable));
does this work?
Insert into AnotherTable
values (fetchColumn1, fetchColumn2, (select * from YetAnotherTable));
Charles
On Thu, Feb 28, 2019 at 4:23 PM Rob Berendt <rob@xxxxxxxxx> wrote:
Newbie to SPL.
Really trying to get the basics of error handling.
Basically I have a loop
Declare c1 cursor for select ...
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ROW_NOT_FOUND = 1;
Open c1;
Fetch c1 into fetchColumn1, fetchColumn2...
SET END_CURSOR = ROW_NOT_FOUND;
WHILE END_CURSOR = 0 DO
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
Note: rows are often not found in YetAnotherTable. In that case I
just want to skip this insert and fetch the next row from the cursor.
Set row_not_found = 0;
Fetch c1 into fetchColumn1, fetchColumn2...
Set END_CURSOR = row_not_found;
End while;
Close C1;
End p1;
The problem is that when it hits the missing row in YetAnotherTable,
it sets SQLCODE, SQLSTATE, ROW_NOT_FOUND, exits the loop and the procedure.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=Dw
ICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2
RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhj
gw&s=qGags8G3G70hJOc_2F9pBtVwcQuLm45V8h6RkCrEjUU&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFg
BU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=b826DjC04pvfzpMiLZNSx1uEuXz7L
xPxhynsnucfoGA&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=9L_APWNJZMl_AlBQWNsMCHo1UqQwEaK3e60A_9EwxBM&e=.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.c
om&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8
HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre9
7RdfUhjgw&s=UpTDzrbNLtQmcFmtf2XeZyzNkxLa11pLIRjt222c8FE&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=b826DjC04pvfzpMiLZNSx1uEuXz7LxPxhynsnucfoGA&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=9L_APWNJZMl_AlBQWNsMCHo1UqQwEaK3e60A_9EwxBM&e=.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=UpTDzrbNLtQmcFmtf2XeZyzNkxLa11pLIRjt222c8FE&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=n78Uldf1hqJVEq5awkQpJz2jVFu2tyeQAe49CyYO_x4&s=DJhRFG7ORVuG6eDupKgz5zaplRDB6NpCFaXFyZ-Xses&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=n78Uldf1hqJVEq5awkQpJz2jVFu2tyeQAe49CyYO_x4&s=pVLYxuLoD4fa0VN2oOpolv0VU5D5-yeaAoNY9-_xwb4&e=.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=n78Uldf1hqJVEq5awkQpJz2jVFu2tyeQAe49CyYO_x4&s=_Y7ruPx75etuC5rYOIqeeF4En9DZTQ01hGoQxUb1YjM&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
