Hi, I get a bit confused from the records you wrote and about joinkey.
Anyway, assuming all keys are 20 bytes long and havingthis input: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8 DIV ORGANIC 1 LOC NJ-HUNTERDON 1 DIV ORGANIC 1 LOC PA-BERKS 1 LOC NJ-HUNTERDON 1 DEP PURCHASING 1 LOC PA-BERKS 1 DEP LEGAL 1 LOC PA-BERKS 2 DEP LEGAL 2 LOC PA-BERKS 1 DEP SHIPPING 1 DEP PURCHASING 1 EMP JOE SMITH 1 1994 10021 DEP PURCHASING 1 EMP BOB ABRAMS 1 2003 10438 DEP PURCHASING 1 EMP APRIL LACOSTE 1 2004 31222 DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 DEP LEGAL 2 EMP SAMUEL DAVIS 2 2013 17284 DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 DEP SHIPPING 1 EMP ZIKAS HARIM 1 2015 29331 with a couple of join you can obtain the following: DIV ORGANIC 1 LOC NJ-HUNTERDON 1 LOC NJ-HUNTERDON 1 DEP PURCHASING 1 DEP PURCHASING 1 EMP JOE SMITH 1 1994 10021 DEP PURCHASING 1 EMP BOB ABRAMS 1 2003 10438 DEP PURCHASING 1 EMP APRIL LACOSTE 1 2004 31222 DIV ORGANIC 1 LOC PA-BERKS 1 LOC PA-BERKS 1 DEP LEGAL 1 DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 LOC PA-BERKS 2 DEP LEGAL 2 DEP LEGAL 2 EMP SAMUEL DAVIS 2 2013 17284 DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 LOC PA-BERKS 1 DEP SHIPPING 1 DEP SHIPPING 1 EMP ZIKAS HARIM 1 2015 29331 that seems to be what you need. This is the JCL: //JOIN01 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //LOC DD * LOC NJ-HUNTERDON 1 DEP PURCHASING 1 LOC PA-BERKS 1 DEP LEGAL 1 LOC PA-BERKS 2 DEP LEGAL 2 LOC PA-BERKS 1 DEP SHIPPING 1 /* //DEP DD * DEP PURCHASING 1 EMP JOE SMITH 1 1994 10021 DEP PURCHASING 1 EMP BOB ABRAMS 1 2003 10438 DEP PURCHASING 1 EMP APRIL LACOSTE 1 2004 31222 DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 DEP LEGAL 2 EMP SAMUEL DAVIS 2 2013 17284 DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 DEP SHIPPING 1 EMP ZIKAS HARIM 1 2015 29331 /* //SORTOUT DD DSN=&&LOCDEP,DISP=(,PASS),SPACE=(TRK,(1,1)) //SORTOU2 DD SYSOUT=* //SYSIN DD * JOINKEYS F1=LOC,FIELDS=(32,20,A) JOINKEYS F2=DEP,FIELDS=(6,20,A) JOIN UNPAIRED REFORMAT FIELDS=(F1:1,80,F2:1,80) SORT FIELDS=COPY OUTFIL FNAMES=SORTOUT OUTFIL FNAMES=SORTOU2 /* //*------------------------------------------------------------------- //JOIN02 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //DIV DD * DIV ORGANIC 1 LOC NJ-HUNTERDON 1 DIV ORGANIC 1 LOC PA-BERKS 1 /* //LOCDEP DD DSN=&&LOCDEP,DISP=(OLD,DELETE) //SORTOUT DD SYSOUT=* //SORTOU2 DD SYSOUT=* //SYSIN DD * JOINKEYS F1=DIV,FIELDS=(32,12,A) JOINKEYS F2=LOCDEP,FIELDS=(6,12,A) JOIN UNPAIRED REFORMAT FIELDS=(F1:1,80,F2:1,160) SORT FIELDS=COPY OUTFIL FNAMES=SORTOUT, SECTIONS=(32,20,HEADER3=(1,80), 112,20,HEADER3=(81,80)), OUTREC=(161,80) OUTFIL FNAMES=SORTOU2 /* The two SORTOU2 output are only to better understand the process. Hope this helps. Regards. Max Il giorno mer 25 set 2019 alle ore 00:24 Mike Schwab < [email protected]> ha scritto: > I would assume the DEPARTMENT and LOCATION tables are fairly static. > How about creating a table of each for your use? Have a run to insert > / update the records from the appropriate source. Then you are left > with one job to parse the employees with these tables. I think they > can even do this with one sort with three flat input files if you > want. > > On Tue, Sep 24, 2019 at 10:07 AM Billy Ashton <[email protected]> > wrote: > > > > Hey everyone (and especially Kolusu): > > I have three different SQL reports that I am trying to use to create a > > consolidated report (it is not possible to do this in a single SQL pass > for > > various processing and ownership reasons). I have been straining my brain > > trying to figure out how to PUSH the key fields into work areas of the > > record so they can sort in order, but I can't seem to come up with the > > right details. > > > > Here is the description: > > 1. These are all hierarchy reports, showing a relationship between a > > Division and Location, a Location and Department, and a Department and > > Employee. Every one of these entities can have one or more versions (we > > call them RecID), and the RecID is independent of the rest of the > hierarchy > > - for instance, Division-rec1 r1 can have Location-rec1 r5 and > > Location-rec2 r3...each item has its own RecID. > > 2. Even though the RecID is independent, the relationship of the > hierarchy > > is consistent. So if there is a Division-rec1 r1 with Location-rec1 r5, > > there will be a Location-rec1 r5 record with its 1 or more departments. > And > > if there is a Location-rec1 r5 with a Department-rec1 r3, there will be a > > Department-rec1 r3 with its employees. > > 3. There are three reports: Division to Location, Location to Department, > > and Department to Employee. I would like to put these all together to > show > > the hierarchy in an easier-to-read format, with every subordinate record > > listed with its superior record. > > > > Maybe an example is easier to understand... > > Given this input from concatenated reports (I have cut out a number of > > unrelated columns - this is only an excerpt): > > F_TP F_NAME ... F_RECID T_TP T_NAME T_RECID START EMP_NO > > DIV ORGANIC 1 LOC NJ-HUNTERDON 1 > > DIV ORGANIC 1 LOC PA-BERKS 1 > > LOC NJ-HUNTERDON 1 DEP PURCHASING 1 > > LOC PA-BERKS 1 DEP LEGAL 1 > > LOC PA-BERKS 2 DEP LEGAL 2 > > LOC PA-BERKS 1 DEP SHIPPING 1 > > DEP PURCHASING 1 EMP JOE SMITH 1 1994 10021 > > DEP PURCHASING 1 EMP BOB ABRAMS 1 2003 10438 > > DEP PURCHASING 1 EMP APRIL LACOSTE 1 2004 31222 > > DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 > > DEP LEGAL 2 EMP SAMUEL DAVIS 2 2013 17284 > > DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 > > DEP SHIPPING 1 EMP ZIKAS HARIM 1 2015 29331 > > > > I would like to see the output in this order: > > DIV ORGANIC 1 LOC NJ-HUNTERDON 1 > > LOC NJ-HUNTERDON 1 DEP PURCHASING 1 > > DEP PURCHASING 1 EMP JOE SMITH 1 1994 10021 > > DEP PURCHASING 1 EMP BOB ABRAMS 1 2003 10438 > > DEP PURCHASING 1 EMP APRIL LACOSTE 1 2004 31222 > > DIV ORGANIC 1 LOC PA-BERKS 1 > > LOC PA-BERKS 1 DEP LEGAL 1 > > DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 > > LOC PA-BERKS 2 DEP LEGAL 2 > > DEP LEGAL 1 EMP SAMUEL DAVIS 1 2013 17284 > > DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 > > LOC PA-BERKS 1 DEP SHIPPING 1 > > DEP SHIPPING 1 EMP ZIKAS HARIM 1 2015 29331 > > > > So, for Div ORGANIC r1, there is a Loc NJ-HUNTERDON r1 and a Loc PA-BERKS > > r1. Then for the first NJ Loc r1, there is one Department PURCHASING r1, > > and in that department (r1) are three Employees. You can also see that > for > > Loc PA-BERKS, there are two LEGAL Deparments - r1 and r2, and these > > departments have different employees. > > > > The columns here are found in these locations on the print records: > > col 2, length 3 > > col 7, len 32 > > col 41, len 4 > > col 46, len 3 > > col 50, len 32 > > col 84, len 4 > > col 96, len 4 > > col 101, len 8 > > The entire input record is 241 bytes long. > > > > The problem I have is how can I tie input record 1 to input rec 3, and > > input rec 3 to recs 7, 8, and 9? I need to be sure the From and To values > > between Hierarchy levels match on the name and the RecID. This small > sample > > shows the challenge, and the total input is in the thousands, so I need > to > > find a way to put this together. > > > > Thank you to Kolusu and anyone else who can direct me to the right > solution. > > > > Billy > > > > ---------------------------------------------------------------------- > > For IBM-MAIN subscribe / signoff / archive access instructions, > > send email to [email protected] with the message: INFO IBM-MAIN > > > > -- > Mike A Schwab, Springfield IL USA > Where do Forest Rangers go to get away from it all? > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to [email protected] with the message: INFO IBM-MAIN > ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [email protected] with the message: INFO IBM-MAIN
