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 <bill00ash...@gmail.com> 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 lists...@listserv.ua.edu 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 lists...@listserv.ua.edu with the message: INFO IBM-MAIN
