Re: SQL how to get Generated Always value on Insert

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
...
"