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

Hi Darryl, add a trigger *BEFORE *INSERT to TARGET_FILE:

if new.id = 0;
// Generate the id using a dtaara
Endif;

And then

INSERT into TARGET_FILE
(ID, FIELD1, FIELD2)
(Select 0, FLD1, FLD2 from SOURCE_FILE)

HTH
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno mer 11 nov 2020 alle ore 17:14 a4g atl <a4ginatl2@xxxxxxxxx> ha
scritto:

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