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
