Re: How to increment a ID field that is not a ID field.

if the source file doesn't have deleted records...or if it doesn't and you
don't mind missing values

INSERT into TARGET_FILE
(ID, FIELD1, FIELD2)
(Select (Select Max(ID) + rrn(s) from TARGET_FILE), FLD1, FLD2 from
SOURCE_FILE S)

Otherwise, you could use ROW_NUMBER()

Charles



On Wed, Nov 11, 2020 at 9:15 AM a4g atl <a4ginatl2@xxxxxxxxx> wrote:

I have a file with fields: ID, field1, field2. ID is the key and is unique.

I have a SQL script to read a view and insert records into this file. ID is
a numeric field so I tried to use the following to auto generate the next
ID number.

INSERT into TARGET_FILE
(ID, FIELD1, FIELD2)
(Select (Select Max(ID) + 1 from TARGET_FILE), FLD1, FLD2 from SOURCE_FILE)

The problem is it builds the ( Select Max(ID) + 1 from TARGET_FILE) upfront
and so the value is the same for each record in the source_file.

Are there suggestions on how to achieve this?
I want to avoid using a RPG program to get this to work.
I am not able to define the PF as a table so the field will remain a
numeric field.

TIA, Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com