Re: Retrieving Last Inserted IDENTITY value

It's easier to use the support site than to dig through InfoCenter.

HTH
Vern

At 12:51 AM 8/18/2006, you wrote:

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.