Re: catch exception within a foreach loop and move to the next row

Thank you, This did it. But also learned some other ways from the previous posts. So thank you everyone for helping me out. On Fri, Feb 5, 2010 at 8:46 AM, Jonathan Leffler <jonathan.leffler@gmail.com> wrote: > On Feb 5, 5:31 am, Jonathan Leffler <jonathan.leff...@gmail.com> > wrote: >> On Feb 4, 2:10 pm, Gentian Hila <genti.t...@gmail.com> wrote: >> > I am trying to populate a table (B ) based with some data from another >> > table (A) but at the same time should not override what it is in >> > there. >> >> > So I created a stored procedure that has a foreach loop similar to this >> >> > FOREACH cs_insert FOR >> > SELECT cust_num INTO v_cust_num FROM A >> > INSERT INTO B(cust_num) VALUES(v_cust_num)>> >> > END FOR >> >> > I was trying to use the ON EXCEPTION on cases when inserting >> > encounters something in there to catch the exception and do nothing >> > but continue inserting the next row, so the FOREACH loop does not >> > fail. >> >> > I tried this [...] >> > This works but the next instruction is not the next row within the >> > FOREACH loop but the END PROCEDURE. >> >> > I also tried [...] >> >> > CREATE PROCEDURE X()>> >> > DEFINE v_cust_num CHAR(20); >> >> > FOREACH cs_insert FOR >> > BEGIN >> >> > ON EXCEPTION >> > END EXCEPTION WITH RESUME >> > SELECT cust_num INTO v_cust_num FROM A >> > INSERT INTO B(cust_num) VALUES(v_cust_num) >> > END >> > END FOR>> >> > END PROCEDURE >> >> > and this way I cannot even create the procedure at all. >> >> > I was thinking just like in java programming with TRY / CATCH clause. >> >> > How can I make the foreach loop move to the row n+1 when it encounters >> > an error on row n? >> >> You are close with the second version - you just need to keep the >> SELECT that drives the FOREACH with the FOREACH, >> and remember to end a FOREACH loop with END FOREACH. >> >> Thus: >> >> CREATE PROCEDURE X()>> >>     DEFINE v_cust_num CHAR(20); >> >>     FOREACH cs_insert FOR SELECT cust_num INTO v_cust_num FROM A >>         BEGIN >>             ON EXCEPTION >>             END EXCEPTION WITH RESUME; >>             INSERT INTO B(cust_num) VALUES(v_cust_num);>>         END >>     END FOREACH >> >> END PROCEDURE > > Minutiae: the semi-colon after RESUME is optional - I don't think its > presence affects anything, but I'm willing to be proved wrong. > The semi-colon after the INSERT is required. > > Yours, > Jonathan Leffler > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >