Re: SQL Next Sequence

I was using a sequence to insert a carton#, not a key, into a set of
records. It turned out I needed to insert the same value into multiple
records so I wanted the value in my rpg program so i could increment it as
I saw fit.

My problem wasn’t retrieving the value, it was that the sequence name was
soft coded.

On Sat, Jun 2, 2018 at 1:33 PM Mihael Schmidt <mihael@xxxxxxxxxxxxxx> wrote:

I am a bit late on the topic but you can just use the corresponding
functions, see

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzseqref.htm
. NEXT VALUE will give you exactly that ... the next value of the sequence.

And to manually get the value of a sequence can come handy when you want
have a unique key across multiple tables. F. e. if you have a customer
table and a prospects table but want to have a unique key across both.
And when a prospect turns to a customer you want to transfer the
prospect to the customer table with keeping the same keep from the
prospects table. (I know it is no longer unique across both tables but
you probably know what I mean. And I don't say that it is a good data
model but it is a valid case.)
My 2 cents.

Mihael

On 02.06.2018 17:28, Jack Callahan wrote:

Curious as to why you want to know the next sequence number.

You don't need to provide the value to add a row to your table- that's

kind

of the point of allowing the system to generate the value.

If you want to know the identity value assigned to a row after it has

been

inserted (so you can use it as a foreign key for other tables that refer

to

this row), something like this should do the trick.

SELECT id INTO :newid
FROM FINAL TABLE
( INSERT INTO yourtable ...)

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD