LAST_IDENTITY (SQL)
Synopsis
LAST_IDENTITY()
Description
The LAST_IDENTITY function returns the %ROWID local variable value. The %ROWID local variable is set to a value in Embedded SQL or ODBC. The %ROWID local variable is not set to a value by Dynamic SQL, the SQL Shell, or the Management Portal SQL interface. Dynamic SQL instead sets a %ROWIDOpens in a new tab object property.
The LAST_IDENTITY function takes no arguments. Note that the argument parentheses are required.
LAST_IDENTITY returns the IDENTITY field value of the last row affected by the current process. If the table has no IDENTITY field, it returns the row ID (%ROWID) of the last row affected by the current process. The returned value is data type INTEGER.
-
For an Embedded SQL INSERT, UPDATE, DELETE or TRUNCATE TABLE statement, LAST_IDENTITY returns the IDENTITY or %ROWID value of the last row modified.
-
For an Embedded SQL cursor-based SELECT statement, LAST_IDENTITY returns the IDENTITY or %ROWID value of the last row retrieved. However, if the cursor-based SELECT statement includes a DISTINCT keyword or a GROUP BY clause, LAST_IDENTITY is not changed; it returns its prior value (if any).
-
For an Embedded SQL single-row (non-cursor) SELECT statement, LAST_IDENTITY is not changed. The prior value (if any) is returned.
At process initiation, LAST_IDENTITY returns NULL. Following a NEW %RowID, LAST_IDENTITY returns NULL.
If no rows were affected by an operation, LAST_IDENTITY is not changed; LAST_IDENTITY returns its prior value (if any). Following a NEW %RowID, invoking LAST_IDENTITY returns NULL, but invoking %ROWID generates an <UNDEFINED> error.
For further details on IDENTITY fields, see CREATE TABLE. Also see %ROWID.
Examples
The following example uses two Embedded SQL programs to return LAST_IDENTITY. The first example creates a new table Sample.Students, The second example populates this table with data, then performs a cursor-based SELECT on the data, returning LAST_IDENTITY for each operation.
Please run the two Embedded SQL programs in the order shown. (It is necessary to use two embedded SQL programs here because embedded SQL cannot compile an INSERT statement unless the referenced table already exists.)
WRITE !,"Creating table"
&sql(CREATE TABLE Sample.Students (
StudentName VARCHAR(30),
StudentAge INTEGER,
StudentID IDENTITY))
IF SQLCODE=0 {
WRITE !,"Created table, SQLCODE=",SQLCODE }
ELSEIF SQLCODE=-201 {
WRITE !,"Table already exists, SQLCODE=",SQLCODE }
WRITE !,"Populating table"
NEW %ROWCOUNT,%ROWID
&sql(INSERT INTO Sample.Students (StudentName,StudentAge)
SELECT Name,Age FROM Sample.Person WHERE Age <= '21')
IF SQLCODE=0 {
WRITE !,%ROWCOUNT," records added, last RowID is ",%ROWID,! }
ELSE {
WRITE !,"Insert failed, SQLCODE=",SQLCODE }
&sql(SELECT LAST_IDENTITY()
INTO :insertID
FROM Sample.Students)
WRITE !,"INSERT Last Identity is: ",insertID,!!
/* Cursor-based SELECT Query */
&sql(DECLARE C1 CURSOR FOR
SELECT StudentName INTO :name FROM Sample.Students
WHERE StudentAge = '17')
&sql(OPEN C1)
QUIT:(SQLCODE'=0)
&sql(FETCH C1)
WHILE (SQLCODE = 0) {
WRITE name," is seventeen",!
&sql(FETCH C1) }
&sql(CLOSE C1)
WRITE !,%ROWCOUNT," records queried, last RowID is ",%ROWID,!
&sql(SELECT LAST_IDENTITY()
INTO :qId)
WRITE !,"SELECT Last Identity is: ",qId,!
&sql(DROP TABLE Sample.Students)