RE: Retrieving Last Inserted IDENTITY value

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.