Re: Increment during insert

Thanks to all who replied...
Because I need the SAME sequence number on multiple rows, I could not get
the desired results with Charles' "Coalesce" solution nor with Scott's
Row_Number function.

Birgitta, thank you for chiming in... But that seems just WAY too complex
for me.

Just to clarify... I have two files that I'm joining to create a 3rd
output file.

The AA file contains one row for each Box
The BB file contains one row for each Item inside Each box
The BX (output) file needs to contain one row for each item inside each
box (same number of rows as BB).
The incrementing box number (I need to generate) needs to increment for
each row in file AA. In the AA file, each row contains a unique Box ID
(field AABID#).

So I was able to do this in Run SQL Scripts by creating a CTE over the AA
file:

with BOXROWS as (
select row_number() over(partition by aacom#, aaord#, aarel#) as boxNbr,
aacom#, aaord#, aarel#, aabid#, aabxtp, aagrc#
from RFPCKRAA
order by aacom#, aaord#, aarel#, aabid#
)
Select 'BX', bbcom#, bbord#, bbrel#, bbbid#, boxNbr, bbprt#, bbqty#, aabxtp
From BOXROWS
Join RFPCKRBB on aabid#=bbbid#
Order by bbcom#, bbord#, bbrel#, bbbid#;

In reality, this will only be executed for a single Company, Order, and
Release (aacom#, aaord#, aarel#) - So there will be a WHERE clause in the
Select statement.

Any reasons to NOT use this method?


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
Birgitta Hauser
Sent: Thursday, January 30, 2020 12:00 PM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Increment during insert

Just try to work with an SEQUENCE OBJECT.
Sequence Objects can be reset!

CREATE OR REPLACE SEQUENCE YOURSCHEMA/YOURSEQ
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CYCLE CACHE 20 NO ORDER ;

Insert into HSCOMMON05.HSSEQ1
Select Next Value for HSCOMMON05.Myseq1, CUSTNO, CUSTNAME1, STREET,
ZIPCODE, CITY, COUNTRY From HSCOMMON05.AddressX;

Insert into OEBOXCBX (bxridc, bxcom#, bxord#, bxrel#, BXBID#, bxbox#)
Select 'BX', bbcom#, bbord#, bbrel#, bbbid#, Next Value for
YOURSCHEMA.YOURSEQ
From RFPCKRBB
Where bbcom#=:inCom and bbord#=:inOrd and bbrel#=:rls and bbqty#>0
Order by BBBID#;

Reset Sequence:
Alter Sequence YOURSCHEMA/YOURSEQ
Data Type INTEGER Restart With 1 Increment By 1 No Order Cycle Minvalue
1
Maxvalue 100000 Cache 20;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
Wilburn
Sent: Donnerstag, 30. Januar 2020 17:16
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Increment during insert

That's what I thought as well...

1. I can't change the file/field definition because legacy pgms are using
it 2. I need it to increment ONLY when the box ID changes (not every
record).

Short of using an SQL cursor and writing a row-at-a-time (incrementing the
box number in my RPG program) - I don't know what else to do.

I suppose SQL doesn't always have an answer (let the flaming begin).

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
Charles Wilt
Sent: Thursday, January 30, 2020 11:07 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Increment during insert

That's not going to work as desired...

an identity won't reset to 1 for each new box number...

Charles

On Thu, Jan 30, 2020 at 9:04 AM Bennett, Steve <sbennett@xxxxxxxxxxxxxxxx>
wrote:

Try this on your field definition:

AUDIT_ID INT NOT NULL
GENERATED ALWAYS AS IDENTITY
( START WITH 1 INCREMENT BY 1
CYCLE)




Thanks,

Steve


Steven Bennett
Senior Systems Analyst

AIT Worldwide Logistics, Inc.
Chicago, Illinois, USA
Office: +1 (630)766-8300 / Ext: 6472
Fax: +1 (630) 250-3708
www.aitworldwide.com


our people deliver.®





-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Patrik Schindler
Sent: Thursday, January 30, 2020 9:52 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Increment during insert

CAUTION: This message originated externally, please use caution when
clicking on links or opening attachments!

Hello Greg,

Am 30.01.2020 um 16:16 schrieb Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

I need to increment a "box number" during the insert, starting at 1
and

incrementing each time a value in the box ID column (BXBID#) changes.

You can define an after-insert-trigger which increments that field
after insert. Also, there's a term for an mysql-like auto-increment
field type, I forgot. It's allowed only once per table. Maybe someone
knows what I'm talking about and can shed some light, even if this is
not some RPG related question.

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://www.pocnet.net/poc-key.asc

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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


**********************************************************************
************************************************


This electronic message transmission contains information from AIT
Worldwide Logistics, Inc. and is confidential and/or legally
privileged and shall remain the property of AIT Worldwide Logistics,
its subsidiaries, affiliates and parent companies. The information is
intended only for the use by the designated person(s) named above. If
you are not the intended recipient, any disclosure, copying,
distribution or use of or any other action or reliance based on the
contents of this information is strictly prohibited. If you received
this electronic transmission in error, please notify the sender listed
above.

Global services provided by AIT Worldwide Logistics are subject to the
applicable terms and conditions published at
http://www.aitworldwide.com/terms-and-conditions.

**********************************************************************
************************************************

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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