Re: Increment during insert

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Try

     Exec Sql
     Insert into OEBOXCBX (bxridc, bxcom#, bxord#, bxrel#, BXBID#, bxbox#)
     Select 'BX', bbcom#, bbord#, bbrel#, bbbid#, coalesce((select
max(bxbox#)
                                           from rfpckrbb B
                                          where b.bxbid# = a.bxbid#
                                           ),0) + 1
    From RFPCKRBB A
     Where bbcom#=:inCom and bbord#=:inOrd and bbrel#=:rls and bbqty#>0
     Order by BBBID#;

Charles

On Thu, Jan 30, 2020 at 8:16 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> I'm inserting rows into a table based on the values in several other
> tables (joined).
>
> 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.
> There are multiple rows with the same Box ID in table RFPCKRBB.
>
>      Exec Sql
>      Insert into OEBOXCBX (bxridc, bxcom#, bxord#, bxrel#, BXBID#, bxbox#)
>      Select 'BX', bbcom#, bbord#, bbrel#, bbbid#, BOX_NUMBER_HERE
>     From RFPCKRBB
>      Where bbcom#=:inCom and bbord#=:inOrd and bbrel#=:rls and bbqty#>0
>      Order by BBBID#;
>
> I'm almost to the point of reverting to SETLL and READE to accomplish this.
>
> Thanks,
> Greg
> --
> 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