1

I ask about alternative or similar query in informix to perform the following:

INSERT INTO days (depcode,studycode,batchnum) values (1,2,3);SELECT SCOPE_IDENTITY();

I want a query to return the SCOPE_IDENTITY() during insertion statement

Curtis
  • 101,612
  • 66
  • 270
  • 352
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392

2 Answers2

2

I know that in t-sql you have the OUTPUT statement? Where [KEY] is the column name of your primary key and @OUT_KEY is a variable you need to declare

INSERT INTO days
(
  depcode,
  studycode,
  batchnum
)
OUTPUT INSERTED.[KEY] INTO @OUT_KEY
VALUES
(
  1,2,3
)

EDIT

For informix you can use 

SELECT DBINFO( 'sqlca.sqlerrd1' )
FROM systables
WHERE tabid = 1;

Presuming your pk column is SERIAL

tom502
  • 691
  • 1
  • 5
  • 19
  • this is the answer, please can u explain what u do .what is `tabid`? – Anyname Donotcare Jul 27 '11 at 11:36
  • 1
    tabid is a field on systables which as an id for each table. it's explained here http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.esqlc.doc/esqlc214.htm The sqlca and sqlerrd1 are explained here http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.esqlc.doc/esqlc214.htm – tom502 Jul 27 '11 at 11:45
0

I use this sql statement Select @@Identity after I inserted rows. It gives me the ID of the last inserted row. I´m using an accessdatabase. I don´t know if it work with your database.

Andre Gross
  • 263
  • 2
  • 8
  • Maybe it didn´t work with informix. What kind of error you get? – Andre Gross Jul 27 '11 at 11:30
  • 1
    Never use @@Identity for this in SQL Server. It wilgive you the wrong answer if anyone ever puts a trigger on the table that inserts to another table with an identity. ANd you may not even know it happened until your data hase been so throughly messed up that it is unfixable. Scope_identity() or the output clasue is what you want to use. – HLGEM Jul 27 '11 at 17:17