Billy,
it was a bit challenging and I'm not 100% sure it's so generalized .....
you've real file, try and let us know.
Here a stream who (in theory) does the needed, I've left different useless
output but understanding the flow:
//*-------------------------------------------------------------------
//SORT01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
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
/*
//SORTOUT DD DSN=&&SORT1,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTOU2 DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(1,80,SEQNUM,5,ZD)
SORT FIELDS=COPY
*SORT FIELDS=(1,10,A,11,10,A,21,10,A,31,10,A),FORMAT=BI
OUTFIL FNAMES=SORTOUT,NODETAIL,REMOVECC,
SECTIONS=(1,10,HEADER3=(1,10,11,10,21,10,31,10,X,81,5,X,C'1'),
11,10,HEADER3=(10X,11,10,21,10,31,10,X,81,5,X,C'2'),
21,10,HEADER3=(20X,21,10,31,10,X,81,5,X,C'3'),
31,10,HEADER3=(30X,31,10,X,81,5,X,C'4'))
OUTFIL FNAMES=SORTOU2,NODETAIL,REMOVECC,
SECTIONS=(1,10,HEADER3=(1,10,11,10,21,10,31,10,X,81,5,X,C'1'),
11,10,HEADER3=(10X,11,10,21,10,31,10,X,81,5,X,C'2'),
21,10,HEADER3=(20X,21,10,31,10,X,81,5,X,C'3'),
31,10,HEADER3=(30X,31,10,X,81,5,X,C'4'))
/*
//*-------------------------------------------------------------------
//SORT02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&SORT1,DISP=(OLD,DELETE)
//SORTK12 DD SYSOUT=*
//SORTK22 DD SYSOUT=*
//SORTK32 DD SYSOUT=*
//SORTK42 DD SYSOUT=*
//SORTK1 DD DSN=&&SORTK1,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTK2 DD DSN=&&SORTK2,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTK3 DD DSN=&&SORTK3,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTK4 DD DSN=&&SORTK4,DISP=(,PASS),SPACE=(TRK,(1,1))
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=SORTK1,INCLUDE=(1,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK2,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK3,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,EQ,C' ',AND,
21,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK4,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,EQ,C' ',AND,
21,10,CH,EQ,C' ',AND,
31,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK12,INCLUDE=(1,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK22,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK32,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,EQ,C' ',AND,
21,10,CH,NE,C' ')
OUTFIL FNAMES=SORTK42,INCLUDE=(1,10,CH,EQ,C' ',AND,
11,10,CH,EQ,C' ',AND,
21,10,CH,EQ,C' ',AND,
31,10,CH,NE,C' ')
/*
//*-------------------------------------------------------------------
//SORTK1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&SORTK1,DISP=(OLD,DELETE)
//SORTOUT DD DSN=&&SORTK1S,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTOU2 DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,10,BI,A)
SUM FIELDS=NONE
OUTFIL FNAMES=SORTOUT
OUTFIL FNAMES=SORTOU2
/*
//*-------------------------------------------------------------------
//SORTK2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&SORTK2,DISP=(OLD,DELETE)
//SORTOUT DD DSN=&&SORTK2S,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTOU2 DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(11,10,BI,A)
SUM FIELDS=NONE
OUTFIL FNAMES=SORTOUT
OUTFIL FNAMES=SORTOU2
/*
//*-------------------------------------------------------------------
//SORTK3 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&SORTK3,DISP=(OLD,DELETE)
//SORTOUT DD DSN=&&SORTK3S,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTOU2 DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(21,10,BI,A)
SUM FIELDS=NONE
OUTFIL FNAMES=SORTOUT
OUTFIL FNAMES=SORTOU2
/*
//*-------------------------------------------------------------------
//SORTALL EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&SORTK1S,DISP=(OLD,DELETE)
// DD DSN=&&SORTK2S,DISP=(OLD,DELETE)
// DD DSN=&&SORTK3S,DISP=(OLD,DELETE)
// DD DSN=&&SORTK4,DISP=(OLD,DELETE)
//SORTOUT DD DSN=&&SORTALL,DISP=(,PASS),SPACE=(TRK,(1,1))
//SORTOU2 DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(42,5,ZD,A),EQUALS
SUM FIELDS=NONE
OUTFIL FNAMES=SORTOUT
OUTFIL FNAMES=SORTOU2
/*
Final output is:
FRUIT ORANGES FLORIDA SKU#1 00001 1
ARIZONA SKU#1 00002 3
SKU#2 00003 4
GRAPES WISCONSIN SKU#3 00004 2
IDAHO SKU#4 00005 3
OREGON SKU#4 00006 3
VEGETABLE CORN IOWA SKU#8 00007 1
KANSAS SKU#8 00008 3
CARROTS VERMONT SKU#9 00009 2
SORT01 put all the key break and the original record number (at the moment
zoned 5, replace it as needed).
SORT02 split the records into 4 datasets, where K1 is not space, where K2
is not space and so on.
SORTK1 removes duplicates (more than one "FRUIT" key, etc.)
SORTK2 removes duplicates (more than one "ORANGES" key, etc.)
SORTK3 removes duplicates (more than one "FLORIDA" key, etc.)
SORTALL put all the records together sorting by orginal record number and
with EQUALS to be sure the Kn priority condition is respected.
Hope this helps and hope Sri is doing well and will be soon with us again.
Best regards.
Max
Il giorno mer 25 set 2019 alle ore 22:03 Billy Ashton <
[email protected]> ha scritto:
> 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
>
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
