RE: Retrieving Last Inserted IDENTITY value

If you're using embedded SQL


exec SQL
   VALUES IDENTITY_VAL_LOCAL() INTO :IVAR;





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 Joe D
Sent: Friday, August 18, 2006 1:52 AM
To: Midrange Systems Technical Discussion
Subject: Re: Retrieving Last Inserted IDENTITY value

Hope this is not too basic, but it would definitely help me 
and others 
here...

I have only used Identity fields for a couple of tools on the 
iSeries, but I 
used the AutoIncrement field a LOT when developing apps for 
Access.  On the 
iSeries apps I always wound up using the Max() technique to 
get the last 
inserted.

That said, I am obviously interested in how 
IDENTITY_VAL_LOCAL is actually 
used.  Can you post a small example?

Many Thanks!
Joe


----- Original Message ----- 
From: "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, August 17, 2006 11:29 AM
Subject: Re: Retrieving Last Inserted IDENTITY value

Thanks.  That's perfect. RH
"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message

news:57FCBE9213FC584692CAAECA3F59426F50B2E8@xxxxxxxxxxxxxxxxxxxxxxxxxx

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.

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

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

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