AW: Identity column

The table reference is necessary if multiple rows must be inserted and the appropriate Identity Values are necessary to know:
Example:

Select MyId, OtherFields ...

From Final Table ( Insert Into YouTable (col1, col2, ... colN) Select ....) x

Order by Input Sequence;

For a single row I normally prefer Identity_Val_Local()

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von j.beckeringh@xxxxxxxxxxxxxxxxxxxxxxxxxx
Gesendet: Tuesday, 01.12 2015 14:05
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: AW: Identity column

From the same SQL Reference:

Alternative to IDENTITY_VAL_LOCAL:
It is recommended that a SELECT FROM INSERT be used to obtain the assigned value for an identity column. See “table-reference” on page 660 for more information.

Joep Beckeringh
Pantheon Automatisering

"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>


01-12-2015 07:09

AW: Identity column

Could using option 1 yield an incorrect result if more than one

process is

adding records to the table simultaneously?

IDENTITY_VAL_LOCAL will return the identity value of the most recent

insert

on the same level. An insert in a different job is or activation group

is

performed on a different level. Even a trigger or a different program

or a

procedure activated in the same job/activation group is considered to
be

on

a different level.
BTW it also works correctly, if the record gets written with native I/O.
In either way the IDENTITY_VAL_LOCAL function should be executed

immediately

after the insert/write operation for a single row.

From the SQL Reference - Release 7.2:
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).
The insert operation has to be issued at the same level; that is, the

value

has to be available locally within the level at which it was assigned

until

replaced by the next assigned value.
A new level is initiated when a trigger, function, or stored procedure

is

invoked. A trigger condition is at the same level as the associated
triggered action.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."

(Les

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

them

and keeping them!"

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.