RE: SPL question about error handling.

Some of these tables are IBM i services and require a parameter from the cursor. No, this parameter cannot be done with a JOIN or WHERE clause. It's a parameter to a UDTF.
Cursor is built around one IBM i service.
YetAnotherTable is another IBM i service which requires a parameter from the cursor. In our example let's call it fetchColumn1.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Thursday, February 28, 2019 6:37 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SPL question about error handling.

what's in YetAnotherTable?

select * doesn't seem to make sense in the context you've given...

Are you expecting to insert a single row or multiple rows into AnotherTable?

Charles

On Thu, Feb 28, 2019 at 4:35 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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
http://www.dekko.com

--
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

--
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