Incrementing Sequence Numbers

[Surkum]

Hello Everyone,

I need a little bit advice how I can get around the following scenario I have.

I have a file which has PO and Line information like the following. I have to read that file and write to another file with the PO but line number should be increments 10 within the same PO number. In other words the target file should have lines increments of 10, 20, 30.... even if the source files lines have odd numbers within each PO. See target file example below. How do I do this? Appreciate help.

Thanks in advance

Surkum

Source  File                               Target File
PO       Line                            PO       Line
1234       1                            1234      10
1234       2                            1234      20 
1234       3                            1234      30
23456      5                           23456      10
23456      6                           23456      20
23456      7                           23456      30

[tomliotta]

Re: Incrementing Sequence Numbers

In RPG, some basic logic might look like this:

READ Source ;
DOW not %EOF( Source ) ;
   If Source.PO <> Target.PO ;
      Target.Line = 0 ;
   EndIf ;
   Target.PO = Source.PO ;
   Target.Line += 10 ;
   WRITE TargetRec ;
   READ Source ;
EndDo ;

[jamief]

Re: Incrementing Sequence Numbers

Why by great Zeus's chin whiskers would you want to take Key fields and change them so they don't match?

[Surkum]

Re: Incrementing Sequence Numbers

Tom,
Thanks a lot.
I did as advised by you. See below the results. Looks like the target file line number is merely increasing regardless of what the PO is. It is not breaking off when the PO changes. What am I doing wrong.?

Source File Target File
PO Line PO Line
315193 1 315193 10
316795 5 316795 20
316795 6 316795 30
316795 7 318138 40
318138 4 318139 50
318139 1 318158 60
318158 7 318174 70
318174 1 318174 80
318174 2 318372 90
318372 1 318435 100
318435 3 318624 110
318624 1 318624 120
318624 2 318697 130
318697 2 318902 140
318902 1 318902 150
318902 2

[Surkum]

Re: Incrementing Sequence Numbers

Jamie,

That's a good question. Unfortunately, the ERP we are migrating the legacy data does not line the PO line number to in 1s an 2s, it need to be in increments of 10.

[B.Hauser]

Re: Incrementing Sequence Numbers

If it is a one shot, you may use the following SQL solution:

1. Create a temporary table with the original key fields and the calculated new sequence:

Create table QTEMP/TmpTable
as (Select (Row_Number() Over(Partition By MySrc Order By MySrc, MyLine)) * 10 as NewLine,
           YourKeySrc, YourKeyLine
      from YourTable a)
With Data;

2. Update the Sequence in original table from the temporary table.

Update YourTable a
   set YourKeyLine = (Select NewLine
                        from TmpTable b
                        Where     a.YourKeySrc  = b.YourKeySrc
                              and a.YourKeyLine = b.YourKeyLine);

Birgitta

[dileep.gare]

Re: Incrementing Sequence Numbers

Below soulution may help u for the above problem


Fsrcfile if e disk
Ftrgtfile uf a e k disk
DTempvar s like(line)
C read srcfile
C dow not %eof(SRCfile)
C po chain trgtfile
C if %found()
C/EXEC SQL
C+ select Max(Trgtline) value into:tempvar from trgtfile
C+ WHERE TRGTPO=:PO
C/End-EXEC
C ELSE
C RESET TEMPVAR
C Endif
c Eval trgtpo=po
C Eval TRGTline=TEMPVAR+10
C write trgtpf
C Read srcfile
C Enddo
C Eval *inlr=*on

[tomliotta]

Re: Incrementing Sequence Numbers

Personally, I would probably do it similar to Birgitta's suggestion. But it might depend on the entire process. If it can be done just with SQL, it'd probably be better in the long run.

I did as advised by you. See below the results. Looks like the target file line number is merely increasing regardless of what the PO is. It is not breaking off when the PO changes. What am I doing wrong.?

I can't tell what is wrong without seeing the code. I can guess that the target PO and LINE values are being set at the wrong times. Running in debug would show if values are set when they should be.

Here is a complete working example with RPG:

     H option(*nodebugio)

     FSRCLINE   if   e           k disk    qualified
     FTGTLINE   o    e             disk    qualified

     D ds1             ds                  likerec( SRCLINE.SrcRF : *input)
     D ds2             ds                  likerec( TGTLINE.TgtRF : *output)

      /free

         read SRCLINE.SrcRF ds1 ;

         DoW  not %eof( SRCLINE ) ;

            if ds1.po <> ds2.po ;
               ds2.line = 0 ;
               ds2.po = ds1.po ;
            endif ;

            ds2.line += 10 ;

            write TGTLINE.TgtRF ds2 ;
            read SRCLINE.SrcRF ds1 ;

         enddo ;

         *Inlr = *On ;
         return ;

      /end-free

It has a minor change just for a tiny performance improvement. The PO value is set inside the IF statement instead of after it, but the result should be the same. I don't have your files and I don't know the names that you used, so I had to create my own sample data. I used SQL to create the tables, but it should work the same if DDS uses a similar structure.

The SQL was like this:

CREATE TABLE MYLIB/SRCLINE (
   PO   CHAR ( 3)       NOT NULL WITH DEFAULT, 
   LINE NUMERIC ( 3, 0) NOT NULL WITH DEFAULT,
   CONSTRAINT PK_PO 
      PRIMARY KEY (PO, LINE)
 )
 RCDFMT SrcRF

INSERT INTO MYLIB/SRCLINE VALUES('001', 1)                      
INSERT INTO MYLIB/SRCLINE VALUES('001', 2)                      
INSERT INTO MYLIB/SRCLINE VALUES('001', 3)
INSERT INTO MYLIB/SRCLINE VALUES('002', 1)
INSERT INTO MYLIB/SRCLINE VALUES('002', 2)

CREATE TABLE MYLIB/TGTLINE (
   PO   CHAR ( 3)       NOT NULL WITH DEFAULT, 
   LINE NUMERIC ( 3, 0) NOT NULL WITH DEFAULT
 )
 RCDFMT TgtRF

After those were run, I compiled the program and ran it. The SRCLINE data was:

 ....+....1
 PO   LINE 
 001     1 
 001     2 
 001     3 
 002     1 
 002     2

And the resulting TGTLINE data was:

 ....+....1
 PO   LINE 
 001    10 
 001    20 
 001    30 
 002    10 
 002    20

[Surkum]

Re: Incrementing Sequence Numbers

Sorry guys could not get back to you all soon. Was sick and did not make to work. I will try all your valuable suggestions and let you know the results. It is a one time effort to move the data from one ERP to another ERP. So I am open for all your ideas. Thanks a lot. Will keep you posted soon.