Re: SPL question about error handling.

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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