Re: Stored Procedure (or SQL) to get a value then update it to +1

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Question for Mike - will you have multiple jobs trying to access this 
file and increment the value? Do you *EXCL allocate the file first?

Using Query Management queries you can update the file in place - 
assuming there is only one record with that key value, something like 
Raul's idea will work -

Put the statement into a source member (record length 91) - statement 
would be

select nextkey + 1 from your/file

Then CRTQMQRY over that source member.

Then STRQMQRY on the QMQRY created, with OUTPUT(*OUTFILE) OUTFILE(YOUR/FILE)

That replaces the record with the new value.

Now you probably want the original value, so you'd FETCH that first, 
maybe. Whatever - different methods can be used here.

Deallocate when done.

HTH
Vern

On 7/11/2012 4:48 PM, Raul A. Jager W. wrote:
> I think there is a "SECUENCE" type of object in SQL, that increments in
> 1 each time it is used.
>
> Other option is to create a view as "select max(usedkey) + 1 from your.file
>
> Mike Wills wrote:
>
> > Stored procedures are not strong in this one.;-)
> >
> > We have a "constant" file that holds the next sequence number for one of
> > our applications. I now need to access that from an external application. I
> > am hoping I can write a stored procedure to essentially
> >
> > SELECT nextkey from CONSTANT
> > UPDATE CONSTANT SET nextkey = nextkey + 1
> >
> > How would I put this one stored procedure?
> >
> >
> > --
> > Mike Wills
> > http://mikewills.me
> >