Thread

1. Re: sequence incrementing twice

   ezra epstein <ee_newsgroup_post@prajnait.com> — 2004-01-14T19:26:18Z

   "dan" <hassanbensober@lycos.com> wrote in message
   news:9aa190bb.0401130958.6a3cfac5@posting.google.com...
   > I have 2 tables, tab1 ( integer incremented sequence , col2, col3 )
   > and tab2 ( integer from tab1, col4, col5 ).  When I call this function
   > to add a record to each table:
   >
   > LOOP
   >         select nextval('sequence') into id_car;  // for looping
   >
   >         INSERT INTO  tab1
   >              VALUES (default, col2, col3);
   >
   >         INSERT INTO tab2
   >              VALUES (currval('sequence'), col3, col4);
   > END LOOP
   >
   > my sequence gets incremented twice.  If I use currval in the select,
   > then it is not yet defined.  I'd love to have the sequence increment
   > only once.
   
   First off, you could instead do:
   
           INSERT INTO tab2
                VALUES (id_car, col3, col4);
   
   Though that won't change the double-increment. For that you need to be sure
   the sequence isn't being invoked somewhere else?  E.g., a trigger or a
   default value or.... ???   (Or, of course, another session...)
   
   == Ezra Epstein
   
   
   
   

2. Re: sequence incrementing twice

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-18T22:18:36Z

   On Wed, 14 Jan 2004, ezra epstein wrote:
   
   > 
   > "dan" <hassanbensober@lycos.com> wrote in message
   > news:9aa190bb.0401130958.6a3cfac5@posting.google.com...
   > > I have 2 tables, tab1 ( integer incremented sequence , col2, col3 )
   > > and tab2 ( integer from tab1, col4, col5 ).  When I call this function
   > > to add a record to each table:
   > >
   > > LOOP
   > >         select nextval('sequence') into id_car;  // for looping
   > >
   > >         INSERT INTO  tab1
   > >              VALUES (default, col2, col3);
   > >
   > >         INSERT INTO tab2
   > >              VALUES (currval('sequence'), col3, col4);
   > > END LOOP
   > >
   > > my sequence gets incremented twice.  If I use currval in the select,
   > > then it is not yet defined.  I'd love to have the sequence increment
   > > only once.
   > 
   > First off, you could instead do:
   > 
   >         INSERT INTO tab2
   >              VALUES (id_car, col3, col4);
   > 
   > Though that won't change the double-increment.
   
   That's because that whole sequence of operations is wrong. A far more sensible
   function would look like:
   
   
   FOR whatever LOOP
   	SELECT INTO myvar nextval(''sequencename'');
   
   	INSERT INTO tab1 (colname1, colname2, colname3)
   		VALUES (myvar, col2, col3);
   
   	INSERT INTO tab2 (col1name2, col2name2, col3name2)
   		VALUES (myvar, col3, col4);
   END LOOP
   
   If you are happy with the assumption that the default in your insert into tab1
   statement does a nextval on the sequence than you can move your select (with
   the correct syntax of course) after the first insert and use the variable name
   as in Dan's answer or just forget it altogether and just do the insert using
   currval as in your original sequence.
   
   > For that you need to be sure
   > the sequence isn't being invoked somewhere else?  E.g., a trigger or a
   > default value or.... ???   (Or, of course, another session...)
   
   In this case I assume the double increment is due to the default keyword on the
   insert into tab1.
   
   
   --
   Nigel Andrews