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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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