%ROWID
When you initialize a process, %ROWID is undefined. When you issue a NEW %ROWID command, %ROWID is reset to undefined. %ROWID is set by the Embedded SQL operations described below. If the operation is not successful, or completes successfully but does not fetch or modify any rows, the %ROWID value remains unchanged from its prior value: either undefined, or set to a value by a previous Embedded SQL operation. For this reason, it is important to NEW %ROWID before each Embedded SQL operation.
%ROWID is set to the RowID of the last row affected by the following operations:
-
INSERT, UPDATE, INSERT OR UPDATE, or DELETE: After a single-row operation, the %ROWID variable contains the system-assigned value of the RowID (Object ID) assigned to the inserted, updated, or deleted record. After a multiple-row operation, the %ROWID variable contains the system-assigned value of the RowID (Object ID) of the last record inserted, updated, or deleted. If no record is inserted, updated, or deleted, the %ROWID variable value is unchanged. TRUNCATE TABLE does not set %ROWID.
-
Cursor-based SELECT: The DECLARE cursorname CURSOR and OPEN cursorname statements do not initialize %ROWID; the %ROWID value is unchanged from its prior value. The first successful FETCH sets %ROWID. Each subsequent FETCH that retrieves a row resets %ROWID to the current RowID value. FETCH sets %ROWID if it retrieves a row of an updateable cursor. An updateable cursor is one in which the top FROM clause contains exactly one element, either a single table name or an updateable view name. If the cursor is not updateable, %ROWID remains unchanged. If no rows matched the query selection criteria, FETCH does not change the prior the %ROWID value (if any). Upon CLOSE or when FETCH issues an SQLCODE 100 (No Data, or No More Data), %ROWID contains the RowID of the last row retrieved.
Cursor-based SELECT with a DISTINCT keyword or a GROUP BY clause does not set %ROWID. The %ROWID value is unchanged from its previous value (if any).
Cursor-based SELECT with an aggregate function does not set %ROWID if it returns only aggregate function values. If it returns both field values and aggregate function values, the %ROWID value for every FETCH is set to the RowID of the last row returned by the query.
-
SELECT with no declared cursor does not set %ROWID. The %ROWID value is unchanged upon the completion of a simple SELECT statement.
In Dynamic SQL, the corresponding %ROWIDOpens in a new tab property returns the RowID value of the last record inserted, updated, or deleted. Dynamic SQL does not return a %ROWID property value when performing a SELECT query.
You can retrieve the current %ROWID from ObjectScript using the following method call:
WRITE $SYSTEM.SQL.GetROWID()
Following an INSERT, UPDATE, DELETE, TRUNCATE TABLE, or Cursor-based SELECT operation, the LAST_IDENTITY SQL function returns the value of the IDENTITY field for the most-recently modified record. If the table does not have an IDENTITY field, this function returns the RowID for the most-recently modified record.
