Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
Hi,
I have input file as below
LRECL=80 FB
Code:
Hello World 01RES
Hello World 01RES
Hello World 01RES
Hello World 01RES
Hello World 02RES
Hello World 02RES
Hello World 02RES
Hello World 02RES
Hello World 02RES
TRAINING 03TER
TRAINING 03TER
TRAINING 03TER
Output report Required in below format
Code:
DEPT COUNT AVERAGE%
--------- ----- ---------
Hello World 04 33.33
Hello World 05 41.66
TRAINING 03 25.00
Average is calculated as (number of occurance of record per group of DEPT and number by total number of records *100)
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
shrivatsa,
The following DFSORT JCL will give you the desired results.
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
TRAINING 03TER
TRAINING 03TER
TRAINING 03TER
//INB DD *
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 01RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
HELLO WORLD 02RES
TRAINING 03TER
TRAINING 03TER
TRAINING 03TER
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(20,1,A)
JOINKEYS F2=INB,FIELDS=(01,1,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,20,F2:2,8)
INREC BUILD=(1,28,X,C'00000001')
SORT FIELDS=(1,19,CH,A)
SUM FIELDS=(30,8,ZD)
OUTREC OVERLAY=(40:(+10000,MUL,30,8,ZD),DIV,21,8,ZD,EDIT=(TT.TT))
OUTFIL REMOVECC,NODETAIL,BUILD=(80X),
HEADER2=('DEPT COUNT AVERAGE%',/,
'-------------------- -------- --------'),
SECTIONS=(1,19,TRAILER3=(1,19,22:30,8,35:40,5))
//JNF1CNTL DD *
INREC BUILD=(1,19,X)
//JNF2CNTL DD *
INREC BUILD=(X,C'00000001')
SUM FIELDS=(2,8,ZD)
//*
The output from this job is
Code:
DEPT COUNT AVERAGE%
-------------------- -------- --------
HELLO WORLD 01 00000004 33.33
HELLO WORLD 02 00000005 41.66
TRAINING 03 00000003 25.00
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
Hi Skolusu,
Very perfect. Thanks for your time in providing the solution.
I was playing around with one file. Can you please tell me what has given you the idea to achieve this solution by using same input file 2 times?
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
shrivatsa wrote:
Hi Skolusu,
Very perfect. Thanks for your time in providing the solution.
I was playing around with one file. Can you please tell me what has given you the idea to achieve this solution by using same input file 2 times?
shrivatsa,
You can still do with 1 file but would require 2 passes of data. I just eliminated the 2 passes and clubbed the solution into 1 pass. Nothing fancy in there as it is just another programming approach.
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
Look at the syntax of JOINKEYS for SyncSort and all will be forgiven, provided you're using the realse of the product which supports the JOINKEYS functionality.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
Look at the syntax of JOINKEYS for SyncSort and all will be forgiven, provided you're using the realse of the product which supports the JOINKEYS functionality.
This statement is misleading. DFSORT supports JNFxCNTL for modifying the records from the input files before they are joined. Syncsort does not support JNFxCNTL. So with Syncsort, you'd actually need multiple steps to do what DFSORT can do in one step.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
gylbharat wrote:
Hi Skolusu, I ran this job but got the below error
gylbharat,
I know you are eager to learn and experiment every possible solution, but you need to understand that you are using syncsort which is a competitor product for DFSORT and not all features of DFSORT are supported by syncsort. 99.99% of the time , I do test my solutions before I post them. So if you are getting an error , then you need to realize that it is your sort product that has the problem and not the solution provided.
I'm a DFSORT developer. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on syncsort.
.jpg)
