On 09 Nov 2012 10:14, Scott Mildenberger wrote:
I have a auto-generated field in a table defined as
ARCHIVENUM INTEGER GENERATED ALWAYS AS IDENTITY
primary key NOT NULL,
When I do an Insert on the table is there a way to get the new
generated value without having to do a Select on that record?
_i IDENTITY_VAL_LOCAL i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscaidentity.htm
"IDENTITY_VAL_LOCAL is a non-deterministic function that returns the
most recently assigned value for an identity column.
>>-IDENTITY_VAL_LOCAL--(--)-----------><
The function has no input parameters. The result is a DECIMAL(31,0)
regardless of the actual data type of the identity column that the
result value corresponds to.
The value returned is the value that was assigned to the identity column
of the table identified in the most recent insert operation (specified
in either an INSERT statement or a MERGE statement) ...
"
_i Selecting inserted values i_ [aka SELECT FROM INSERT]
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafysfins.htm
"You can retrieve the values for rows that are being inserted by
specifying the INSERT statement in the FROM clause of a SELECT statement.
When you insert one or more rows into a table, you can select the result
rows of the insert operation. These rows include any of the following
values:
* The value of any generated column, such as identity, ROWID, or
row change timestamp columns
* Any default values used for columns
* All values for all rows inserted by a multiple-row insert operation
* Values that were changed by a before insert trigger
...
"
