RE: Retrieving Last Inserted IDENTITY value

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Ryan,

You're looking for the IDENTITY_VAL_LOCAL function.

Note: it returns the last identity value assigned _in_the_job_ calling
the function.

So if you have multiple jobs running, and one calls IDENTITY_VAL_LOCAL,
it gets the last identity assigned in that job.  It doesn't matter what
identities were assigned in the other jobs.

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
> Sent: Thursday, August 17, 2006 2:09 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Retrieving Last Inserted IDENTITY value
>
> Is there a DB2/400 function to retrieve the last IDENTITY 
> value inserted
> into a table?   For MS SQL there is the SCOPE_IDENTITY() 
> function that will
> show the last identity value inserted into any table within 
> the scope of the
> current batch.
>
> Is there anything like that for AS400?  I am trying to avoid a select
> MAX(ID) - which is resource intensive and may return 
> something inserted
> right after me.
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.