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 similiar 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 scenario
CREATE PROCEDURE X()

DEFINE v_cust_num CHAR(20);

BEGIN

ON EXCEPTION
END EXCEPTION WITH RESUME

FOREACH cs_insert FOR
SELECT cust_num INTO v_cust_num FROM A
INSERT INTO B(cust_num) VALUES(v_cust_num)

END FOR

END

END PROCEDURE

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?

Thank you,

VERSION INFORMATION PLEASE!!!!!!!!!!!!!!!!!!!!!!!!!!!!

IDS 11.50 has the new UPSERT statement that will update or insert. If you
have an earlier version of IDS you can use the sqlupsert utility in Jonathan
Leffler's sqlcmd package.

If you mean to ignore rows that are already in the target you can use my
dbcopy utility which has an options to ignore INSERT errors. Dbcopy is in
the utils2_ak package.

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Thu, Feb 4, 2010 at 5:10 PM, Gentian Hila <genti.tech (AT) gmail (DOT) com> wrote:

Thanks,

Right now I use 9.40. We are upgrading in 11.5 in about 2 months.


Yes, I want to ignore rows that are already in the target.

But this is will be a temporary table that will be created once a
month for a report. Not a database copy though.




On Thu, Feb 4, 2010 at 8:13 PM, Art Kagel <art.kagel (AT) gmail (DOT) com> wrote:

i would use violations for this eq

start violation table for b;
create unique index ixie on b(cust_num) filtering without error;

Then do the foreach;
the duplicates will be stored in the violations table... so no need
for exceptions..

when done set the index enabled and stop violations.
the diag and vio tables can be dropped later if not needed..

please check the manual i may have a typo in the syntax ... am getting
older...

Superboer



On 5 feb, 04:45, Gentian Hila <genti.t... (AT) gmail (DOT) com> wrote:

On Feb 4, 2:10*pm, Gentian Hila <genti.t... (AT) gmail (DOT) com> wrote:
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


That compiles.

Yours,
Jonathan Leffler

On Feb 5, 5:31*am, Jonathan Leffler <jonathan.leff... (AT) gmail (DOT) com>
wrote:
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

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 (AT) gmail (DOT) com> wrote: