Create multiple records based on one record within a file.



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Create multiple records based on one record within a file.

Postby chillmo » Thu Aug 11, 2022 3:54 am

Team,

I hope everyone is doing well. I have a requirement to create a couple records based on one field from the input file (customer number).

Sample input file:

11177777777777Y
11199999999999Y
11188888888888Y
 


Desired output file:

11177777777777YCOKE
11177777777777NSPRITE
11199999999999YCOKE
11199999999999NSPRITE
11188888888888YCOKE
11188888888888YSPRITE
 


I made this work by using OUTFIL, creating two different files while using the same input file, with different BUILD parms, then merging the files.

I want to know if this can be done in one pass using sort.

Any assistance would be greatly appreciated.

Thanks!
chillmo
 
Posts: 11
Joined: Wed Aug 30, 2017 8:56 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Create multiple records based on one record within a fil

Postby sergeyken » Thu Aug 11, 2022 5:34 pm

You need to use JOINKEYS for two separate inputs, to join the records all-to-all:

//......
//LEFT     DD  *
11177777777777Y
11199999999999Y
11188888888888Y
//*
//RIGHT    DD  *
YCOKE
NSPRITE
//*
//SYSIN    DD  *
 JOINKEYS F1=LEFT,FIELDS=(81,1,A)    JOIN ON THE ADDED FIELD
 JOINKEYS F2=RIGHT,FIELDS=(81,1,A)    JOIN ON THE ADDED FIELD
 REFORMAT FIELDS=(F1:1,14,F2:1,14)     11177777777777 <-> YCOKE
 INREC OVERLAY=(80:X)         EXTEND TO NORMAL SIZE
 SORT FIELDS=COPY
 END
//*
//JNF1CNTL DD  *
 INREC OVERLAY=(81:X)   ADD THE FIELD TO JOIN ON
//*
//JNF2CNTL DD  *
 INREC OVERLAY=(81:X)   ADD THE FIELD TO JOIN ON
//*
 
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times

Re: Create multiple records based on one record within a fil

Postby chillmo » Thu Aug 11, 2022 9:54 pm

Thanks sergeyken for the prompt response.

This worked to perfection and I was able to get rid of my two-step approach. However, the business added a new wrinkle.

New input file:

11177777777777YN 777
11199999999999YN 999
11188888888888YN 888
 


New output file:

Account Number, Invoice Number, Sale, Word, New Value
Hello,111,77777777777,,SPRITE, ,777
Hello,111,77777777777,,COKE, ,Y
Hello,111,99999999999,,SPRITE, ,999
Hello,111,99999999999,,COKE, ,Y
Hello,111,88888888888,,SPRITE, ,888
Hello,111,88888888888,,COKE, ,Y
 


I can add the header via the HEADER parm on the OUTFIL statement in addition to adding the word Hello to each record but NOT sure how to add the new value from input file to only the first record.
chillmo
 
Posts: 11
Joined: Wed Aug 30, 2017 8:56 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Create multiple records based on one record within a fil

Postby sergeyken » Fri Aug 12, 2022 1:02 am

What is "new value from input file"?
Where it comes from?
How it looks like?

Etc. etc. etc...
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times

Re: Create multiple records based on one record within a fil

Postby chillmo » Fri Aug 12, 2022 2:09 am

It's the 3-digit value at the end of the 1st file:

11177777777777YN 777 <--------
11199999999999YN 999 <--------
11188888888888YN 888 <--------
 


Desired output file:

Msg, Account Number, Invoice Number, Sale, Word, New Value
Hello,111,77777777777,,SPRITE,777
Hello,111,77777777777,,COKE,Y
Hello,111,99999999999,,SPRITE,999
Hello,111,99999999999,,COKE,Y
Hello,111,88888888888,,SPRITE,888
Hello,111,88888888888,,COKE, ,Y
 
chillmo
 
Posts: 11
Joined: Wed Aug 30, 2017 8:56 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Create multiple records based on one record within a fil

Postby sergeyken » Fri Aug 12, 2022 2:23 am

1. Renumber the records from F2 (using SEQNUM)

2. Join two input streams as before

3. Use IFTHEN in either OUTREC, or OUTFIL, - to reformat the final record based on record number of F2. For number 1 append the “new field”, for all other numbers (if more than two) do not append the “new field”
Javas and Pythons come and go, but JCL and SORT stay forever.

These users thanked the author sergeyken for the post:
chillmo (Fri Aug 12, 2022 8:28 am)
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post