Re: How to get next serial value

rory@inetnebr.com (Rory Reynoldson) wrote: : :Quick question about Informix's serial datatypes (I'm used to working with :Oracle sequences). : :In Oracle, I would have a table OTAB (fld1 number) and sequence OTAB_SEQ :and insert as: : insert into OTAB (fld1) values (OTAB_SEQ.nextval); : :In Informix, I would have a table ITAB (fld1 serial) and insert as: : insert into ITAB (fld1) values (0); : :Both give me unique numbers... : :But, in Oracle if I want the unique number before the insert I would do: : select OTAB_SEQ.nextval from dual; :and the insert becomes : insert into OTAB (fld1) values (:some_var); : :Is there a similar thing to do in Informix? Basically I want the unique :number without inserting and then reselecting to retrieve the unique number :(We got lazy under Oracle:).. There is no sequence number generator in Informix that's separate from the serial datatype. You have at least two options: 1. You can insert first and then either use the sqlca record if it's available in whatever language you use, or use dbinfo in a select statement to retreive the last serial value inserted in your session. This is the best way if there is a column that shall contain a unique numeric value over all rows in the table. That's the main purpose of the serial datatype. 2. You can use the mechanism in 1. to implement a sequence generator using a table that contains nothing but one column of type serial. You insert into this table first and retreive the serial number as above. You can delete all rows in this table after every insert if you want,so the table never grows. This can all be easily implemented as a stored procedure so it would work more or less exactly like your description of the Oracle unique number generator. We do this in our application for several purposes. One is when we need a simple sequence number, but with a check digit added. Another is if the sequence should be unique within a subset of rows, such as invoice numbers for different companies, perhaps for credit notes and invoices and similar cases. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org