Thank you for this, Max; with a couple tweaks on the key length, it works just as I need it!
Have a great day! Billy On Wed, Sep 25, 2019 at 4:42 AM Massimo Biancucci <[email protected]> wrote: > 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 > ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [email protected] with the message: INFO IBM-MAIN
