OK, now for another twist on a request from the same department. I have a
flat file that was dumped from a VSAM file, and it contains records with
major, medium and minor breaks. I know there is a way in SORT to suppress
printing the duplicate header values, but I spent an hour in the manual and
can't find it.
For example, with this data
.........+.........+.........+
Fruit Oranges Florida Sku#1
Fruit Oranges Arizona Sku#1
Fruit Oranges Arizona Sku#2
Fruit Grapes Wisconsin Sku#3
Fruit Grapes Idaho Sku#4
Fruit Grapes Oregon Sku#4
Vegetable Corn Iowa Sku#8
Vegetable Corn Kansas Sku#8
Vegetable Carrots Vermont Sku#9
I want to see it reported like this:
Fruit Oranges Florida Sku#1
Arizona Sku#1
Sku#2
Grapes Wisconsin Sku#3
Idaho Sku#4
Oregon Sku#4
Vegetable Corn Iowa Sku#8
Kansas Sku#8
Carrots Vermont Sku#9
As I said, I think it is easy to suppress these 3 key field duplicates, but
I am too new at this reporting. Any suggestions here?
Thanks!
Billy
On Wed, Sep 25, 2019 at 10:51 AM Billy Ashton <[email protected]>
wrote:
> 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
