on exception in SPL

The code below does not work. I was trying to create the temp if it exists, otherwise flush it. Dropping it and falling into the create is ok, too. Is it possible to do something like this? BEGIN ON EXCEPTION IN (-958) set err_num if err_num = -958 then delete from pos_to_gen where 1=1;else create temp table pos_to_gen( reqn_no integer, disp_no integer, matcode char(20), cusno char(15), dropshp char(1), qtytogen integer, ordate datetime year to day, reqd_date datetime year to day, venno char(15), vennam char(35), line_no integer, doc_no integer, matid integer, specpo integer, rg_opnpo integer, aqid integer ) with no log;end if end exception with resume; END sending to informix-list

Bill Hamilton wrote: > The code below does not work. > I was trying to create the temp if it exists, otherwise flush it. > Dropping it and falling into the create is ok, too. > Is it possible to do something like this? > > BEGIN > ON EXCEPTION IN (-958) set err_num > > if err_num = -958 then > delete from pos_to_gen where 1=1;> else > create temp table pos_to_gen( > reqn_no integer, > disp_no integer, > matcode char(20), > cusno char(15), > dropshp char(1), > qtytogen integer, > ordate datetime year to day, > reqd_date datetime year to day, > venno char(15), > vennam char(35), > line_no integer, > doc_no integer, > matid integer, > specpo integer, > rg_opnpo integer, > aqid integer > ) with no log;> end if > > end exception with resume; > END (a) In what sense doesn't it work? What is the error message? (b) What exactly are you trying to do? The CREATE TEMP TABLE statement is part of the exception block - the code that is handling the error. You need to execute the statement outside the exception block to trigger the error: BEGIN ON EXCEPTION IN (-958) DELETE FROM pos_to_gen;END EXCEPTION WITH RESUME; CREATE TEMP TABLE pos_to_gen ( ... ) WITH NO LOG;END That's unvalidated syntax -- check for the placement of semi-colons in particular -- but I'm fairly sure that's the trouble too. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

This is working, it just not doing what you think it should the temp table can never be created 'cos you need a 958 to trigger the exception and the you can only delete from the table because of the conditional test. You need to review the logic. If the table is normally there then I would delete outside of the exception and catch the table doesn't exist error with a resumable exception, something along the lines of on exception (table does't exist) end exception with resume drop table create table the inverse logic would be on exception (table does't exist) drop table end exception with resume create table or similar Bill Hamilton wrote: > The code below does not work. > I was trying to create the temp if it exists, otherwise flush it. > Dropping it and falling into the create is ok, too. > Is it possible to do something like this? > > BEGIN > ON EXCEPTION IN (-958) set err_num > > if err_num = -958 then > delete from pos_to_gen where 1=1;> else > create temp table pos_to_gen( > reqn_no integer, > disp_no integer, > matcode char(20), > cusno char(15), > dropshp char(1), > qtytogen integer, > ordate datetime year to day, > reqd_date datetime year to day, > venno char(15), > vennam char(35), > line_no integer, > doc_no integer, > matid integer, > specpo integer, > rg_opnpo integer, > aqid integer > ) with no log;> end if > > end exception with resume; > END > > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #