geting the last record's field value

I am trying to get the last serial value in order to insert another record which would have as its value the prior records cust_id field (which is a serial field type) value + 1. Does anyone know of a way to do this? I have tried using select dbinfo('sqlca.sqlerrd1') from systables where tabid = 122 but I keep on getting 0. Sent via Deja.com http://www.deja.com/ Before you buy.

cornelhughes@netscape.net wrote: > I am trying to get the last serial value in order to insert another > record which would have as its value the prior records cust_id field > (which is a serial field type) value + 1. Does anyone know of a way to > do this? This is a very Oracle-ish way of doing what you want to accomplish it is not how one does this in Informix. Read on. > I have tried using select dbinfo('sqlca.sqlerrd1') from systables where > tabid = 122 but I keep on getting 0. Looks like you are trying to use dbinfo to ascertain the next serial number to be assigned to rows added to tabid 122. Not the way things are done around here. Just insert a row with cust_id = 0 and the engine will take care of assigning the next available value! That is what SERIAL type columns do and how they are used! Then if you need the serial value that was assigned to that just inserted row you THEN do: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1; -- Note that ANY tabid will do so long as it resolves to a single returned row! You -- do not have to specify the tabid of the table you have just inserted into, the dbinfo -- ('sqlca.sqlerrd1') is just returning a value from an Informix internal data structure, -- sqlca, that contains the last serial number assigned by a row inserted by the -- current session. Indeed if you are using ESQL/C as your front-end (or 4GL) -- you can, instead, just say: assigned_cust_id = sqlca.sqlerrd[1]; -or in 4GL (since arrays are 1 based): LET assigned_cust_id = sqlca.sqlerrd[2] You do NOT get the serial number first and then assign it in an INSERT, you insert a row then ascertain what serial value was assigned to that row. If you still do not get it try reading the FM (Guide to SQL Reference). Art S. Kagel

ignore my question since the aswer lies in the meaning of a serial datatype. All I have to do is don't touch it on inserts In article <8dfe22$vk1$1@nnrp1.deja.com>, cornelhughes@netscape.net wrote: > I am trying to get the last serial value in order to insert another > record which would have as its value the prior records cust_id field > (which is a serial field type) value + 1. Does anyone know of a way to > do this? > > I have tried using select dbinfo('sqlca.sqlerrd1') from systables where > tabid = 122 but I keep on getting 0. > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.