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

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