Re: Trying to put three reports together with SORT
Billy, even simpler: //SORT01 EXEC PGM=SORT //SYSOUTDD SYSOUT=* //SORTIN DD * FRUIT ORANGES FLORIDA SKU#1 FRUIT ORANGES ARIZONA SKU#1 FRUIT ORANGES ARIZONA SKU#2 FRUIT GRAPESWISCONSIN SKU#3 FRUIT GRAPESIDAHO SKU#4 FRUIT GRAPESOREGONSKU#4 VEGETABLE CORN IOWA SKU#8 VEGETABLE CORN KANSASSKU#8 VEGETABLE CARROTS VERMONT SKU#9 /* //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)) //SORTK12 DD SYSOUT=* //SORTK22 DD SYSOUT=* //SORTK32 DD SYSOUT=* //SORTK42 DD SYSOUT=* //SYSIN DD * INREC FIELDS=(1,80,SEQNUM,5,ZD) SORT FIELDS=COPY OUTFIL FNAMES=SORTK1,NODETAIL,REMOVECC, SECTIONS=(1,10,HEADER3=(1,10,11,10,21,10,31,10,X,81,5,X,C'1')) OUTFIL FNAMES=SORTK2,NODETAIL,REMOVECC, SECTIONS=(11,10,HEADER3=(10X,11,10,21,10,31,10,X,81,5,X,C'2')) OUTFIL FNAMES=SORTK3,NODETAIL,REMOVECC, SECTIONS=(21,10,HEADER3=(20X,21,10,31,10,X,81,5,X,C'3')) OUTFIL FNAMES=SORTK4,NODETAIL,REMOVECC, SECTIONS=(31,10,HEADER3=(30X,31,10,X,81,5,X,C'4')) OUTFIL FNAMES=SORTK12,NODETAIL,REMOVECC, SECTIONS=(1,10,HEADER3=(1,10,11,10,21,10,31,10,X,81,5,X,C'1')) OUTFIL FNAMES=SORTK22,NODETAIL,REMOVECC, SECTIONS=(11,10,HEADER3=(10X,11,10,21,10,31,10,X,81,5,X,C'2')) OUTFIL FNAMES=SORTK32,NODETAIL,REMOVECC, SECTIONS=(21,10,HEADER3=(20X,21,10,31,10,X,81,5,X,C'3')) OUTFIL FNAMES=SORTK42,NODETAIL,REMOVECC, SECTIONS=(31,10,HEADER3=(30X,31,10,X,81,5,X,C'4')) /* //SORT02 EXEC PGM=SORT //SYSOUTDD SYSOUT=* //SORTIN DD DSN=&&SORTK1,DISP=(OLD,DELETE) // DD DSN=&&SORTK2,DISP=(OLD,DELETE) // DD DSN=&&SORTK3,DISP=(OLD,DELETE) // DD DSN=&&SORTK4,DISP=(OLD,DELETE) /* //SORTOUT DD SYSOUT=* //SYSIN DD * SORT FIELDS=(42,5,ZD,A),EQUALS SUM FIELDS=NONE /* Output is: FRUIT ORANGES FLORIDA SKU#1 1 1 ARIZONA SKU#1 2 3 SKU#2 3 4 GRAPESWISCONSIN SKU#3 4 2 IDAHO SKU#4 5 3 OREGONSKU#4 6 3 VEGETABLE CORN IOWA SKU#8 7 1 KANSASSKU#8 8 3 CARROTS VERMONT SKU#9 9 2 Same as before, duplicate DD to "see what happens" and RECNUM maybe small and dummy fields after RECNUM. Best regards. Max Il giorno gio 26 set 2019 alle ore 15:15 Massimo Biancucci < mad4...@gmail.com> ha scritto: > 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 > //SYSOUTDD SYSOUT=* > //SORTIN DD * > FRUIT ORANGES FLORIDA SKU#1 > FRUIT ORANGES ARIZONA SKU#1 > FRUIT ORANGES ARIZONA SKU#2 > FRUIT GRAPESWISCONSIN SKU#3 > FRUIT GRAPESIDAHO SKU#4 > FRUIT GRAPESOREGONSKU#4 > VEGETABLE CORN IOWA SKU#8 > VEGETABLE CORN KANSASSKU#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 > //SYSOUTDD 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, >
Re: Trying to put three reports together with SORT
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 //SYSOUTDD SYSOUT=* //SORTIN DD * FRUIT ORANGES FLORIDA SKU#1 FRUIT ORANGES ARIZONA SKU#1 FRUIT ORANGES ARIZONA SKU#2 FRUIT GRAPESWISCONSIN SKU#3 FRUIT GRAPESIDAHO SKU#4 FRUIT GRAPESOREGONSKU#4 VEGETABLE CORN IOWA SKU#8 VEGETABLE CORN KANSASSKU#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 //SYSOUTDD 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 //SYSOUTDD 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 //SYSOUTDD 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 //SYSOUTDD 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 //SYSOUTDD 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 1 1 ARIZONA SKU#1 2 3 SKU#2 3 4 GRAPESWISCONSIN SKU#3 000
Re: Trying to put three reports together with SORT
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 GrapesWisconsin Sku#3 Fruit GrapesIdaho Sku#4 Fruit GrapesOregonSku#4 Vegetable Corn Iowa Sku#8 Vegetable Corn KansasSku#8 Vegetable Carrots Vermont Sku#9 I want to see it reported like this: Fruit Oranges Florida Sku#1 Arizona Sku#1 Sku#2 GrapesWisconsin Sku#3 Idaho Sku#4 OregonSku#4 Vegetable Corn Iowa Sku#8 KansasSku#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 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 > 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 ORGANIC1 LOC NJ-HUNTERDON 1 >> DIV ORGANIC1 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 HARIM1 2015 29331 >> >> with a couple of join you can obtain the following: >> >> DIV ORGANIC1 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 ORGANIC1 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 HARIM1 2015 29331 >> >> that seems to be what you need. >> >> This is the JCL: >> >> //JOIN01 EXEC PGM=SORT >> //SYSOUTDD 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 HARIM1 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 >> //SYSOUTDD SYSOUT=* >> //DIV DD * >> DIV ORGANIC1 LOC NJ-HUNTERDON 1 >> DIV ORGANIC1 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 >
Re: Trying to put three reports together with SORT
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 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 ORGANIC1 LOC NJ-HUNTERDON 1 > DIV ORGANIC1 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 HARIM1 2015 29331 > > with a couple of join you can obtain the following: > > DIV ORGANIC1 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 ORGANIC1 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 HARIM1 2015 29331 > > that seems to be what you need. > > This is the JCL: > > //JOIN01 EXEC PGM=SORT > //SYSOUTDD 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 HARIM1 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 > //SYSOUTDD SYSOUT=* > //DIV DD * > DIV ORGANIC1 LOC NJ-HUNTERDON 1 > DIV ORGANIC1 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 < > mike.a.sch...@gmail.com> 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 > > 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: > >
Re: Trying to put three reports together with SORT
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 ORGANIC1 LOC NJ-HUNTERDON 1 DIV ORGANIC1 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 HARIM1 2015 29331 with a couple of join you can obtain the following: DIV ORGANIC1 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 ORGANIC1 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 HARIM1 2015 29331 that seems to be what you need. This is the JCL: //JOIN01 EXEC PGM=SORT //SYSOUTDD 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 HARIM1 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 //SYSOUTDD SYSOUT=* //DIV DD * DIV ORGANIC1 LOC NJ-HUNTERDON 1 DIV ORGANIC1 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 < mike.a.sch...@gmail.com> 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 > 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 ow
Re: Trying to put three reports together with SORT
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 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_NAMET_RECID START EMP_NO > DIV ORGANIC1 LOC NJ-HUNTERDON1 > DIV ORGANIC1 LOC PA-BERKS1 > 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 SHIPPING1 > 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 DAVIS1 2013 17284 > DEP LEGAL 2 EMP SAMUEL DAVIS2 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 ORGANIC1 LOC NJ-HUNTERDON1 > 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 ORGANIC1 LOC PA-BERKS1 > LOC PA-BERKS 1 DEP LEGAL 1 > DEP LEGAL 1 EMP SAMUEL DAVIS1 2013 17284 > LOC PA-BERKS 2 DEP LEGAL 2 > DEP LEGAL 1 EMP SAMUEL DAVIS1 2013 17284 > DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 > LOC PA-BERKS 1 DEP SHIPPING1 > 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 IB
Trying to put three reports together with SORT
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_NAMET_RECID START EMP_NO DIV ORGANIC1 LOC NJ-HUNTERDON1 DIV ORGANIC1 LOC PA-BERKS1 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 SHIPPING1 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 DAVIS1 2013 17284 DEP LEGAL 2 EMP SAMUEL DAVIS2 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 ORGANIC1 LOC NJ-HUNTERDON1 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 ORGANIC1 LOC PA-BERKS1 LOC PA-BERKS 1 DEP LEGAL 1 DEP LEGAL 1 EMP SAMUEL DAVIS1 2013 17284 LOC PA-BERKS 2 DEP LEGAL 2 DEP LEGAL 1 EMP SAMUEL DAVIS1 2013 17284 DEP LEGAL 2 EMP ROSEMARY BLUE 2 2017 24318 LOC PA-BERKS 1 DEP SHIPPING1 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
