Fields and position:
Account No : 1 - 5
Country Code : 7 - 10
Cancel code : 11 - 12
Sold Status : 14
Amount : 16 - 18
Code:
12345 USAL 05 S 500
23879 BRPL 05 N 300
89823 USGE 04 N 700
89980 USAL 05 S 900
89980 USKA 09 S 100
Report required:
- If Country code starts with 'U', then its a US account.
- Accounts with Cancel code 05 are Cancelled, everything else is not cancelled
Code:
ACCOUNT STATUS CANCEL STATUS TOT ACCOUNTS TOT AMOUNT
-------------- ------------- ------------ ----------
SOLD CANCELLED 2 1400
SOLD NOT CANCELLED 1 100
US ACCOUNTS CANCELLED 2 1400
US ACCOUNTS NOT CANCELLED 2 800
NON US CANCELLED 1 300
NON US NOT CANCELLED 0 0
I tried using icetool, but i was not sure on handling so many conditions in one report. Really appreciate any help, pointers. Thanks in advance.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Why is your TOT AMOUNT left-justified?
Use INREC to put a count and amount on each record for each combination (zero for the ones which don't count on that record, so WHEN=INIT to set all to zero to start with) then you have a multi-line report, TRAILER1, with headings you code and with TOT(AL) for the fields you have created, NODETAIL as you just want a summary.
You can do it with OUTFIL reporting features, or ICETOOL with a USING. Show what you've tried.
Use INREC to put a count and amount on each record for each combination (zero for the ones which don't count on that record, so WHEN=INIT to set all to zero to start with) then you have a multi-line report, TRAILER1, with headings you code and with TOT(AL) for the fields you have created, NODETAIL as you just want a summary.
You can do it with OUTFIL reporting features, or ICETOOL with a USING. Show what you've tried.
Sorry about the left justified, that was a typo.
Here is the code i had tried for just one condition and it was giving syntax error:
Code:
//STEP0100 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
12345 USAL 05 S 500
23879 BRPL 05 N 300
89823 USGE 04 N 700
89980 USAL 05 S 900
89980 USKA 09 S 100
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN) TO(OUT) USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(15,1,CH,A,12,2,CH,A)
SUM FIELDS=(17,3,ZD)
OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
HEADER2=('ACCOUNT STATUS CANCEL STATUS TOT ACCOUNTS',
' TOTAL AMOUNT',/,
'-------------- ------------- ------------',
' ------------'),
OUTREC=(C'SOLD CANCELLED',
COUNT=(M10,LENGTH=5),17,3)
/*
Joined: 02 Jan 2009 Posts: 115 Location: Hyderabad
Hi,
Please see if the below sort step can help you.
The only thing I am ndidn't do is; you wanted to split some records into two. Ex First record is a US one and is sold. For this you needed 2 records. My code doesn't do this.
For that, you may have to modify the WHEN condition to have diff possibilities:
When US,05,S - overlay
When non US, 05,s, overlay .....
Code:
//SORT1 EXEC PGM=SORT
//SORTIN DD *
12345 USAL 05 S 00500
23879 BRPL 05 N 00300
89823 USGE 04 N 00700
89980 USAL 05 S 00900
89980 USKA 09 S 00100
/*
//OUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=(7,2,CH,EQ,C'US'),
OVERLAY=(24:C'1',25:C'US ACCOUNTS'),HIT=NEXT),
IFTHEN=(WHEN=(7,2,CH,NE,C'US'),
OVERLAY=(24:C'1',25:C'NON US '),HIT=NEXT),
IFTHEN=(WHEN=(12,2,ZD,EQ,05),
OVERLAY=(24:C'1',50:C'CANCELLED '),HIT=NEXT),
IFTHEN=(WHEN=(12,2,ZD,NE,05),
OVERLAY=(24:C'1',50:C'NOT CANCELLED'))
SORT FIELDS=(26,15,CH,A,50,15,CH,A)
SUM FIELDS=(17,5,ZD,24,1,ZD)
OUTFIL FNAMES=OUT,
HEADER2=('ACCOUNT-STATUS CANCEL-STATUS COUNT TOTAL-AMOUNT',/,
'-------------- ------------- ----- ------------'),
OUTREC=(25,15,1X,50,15,1X,24,1,6X,17,5,5X)
Here is the output
Code:
ACCOUNT-STATUS CANCEL-STATUS COUNT TOTAL-AMOUNT
-------------- ------------- ----- ------------
NON US CANCELLED 1 00300
US ACCOUNTS CANCELLED 2 01400
US ACCOUNTS NOT CANCELLED 2 00800
The only thing I am ndidn't do is; you wanted to split some records into two. Ex First record is a US one and is sold. For this you needed 2 records. My code doesn't do this.
For that, you may have to modify the WHEN condition to have diff possibilities:
When US,05,S - overlay
When non US, 05,s, overlay .....
Code:
//SORT1 EXEC PGM=SORT
//SORTIN DD *
12345 USAL 05 S 00500
23879 BRPL 05 N 00300
89823 USGE 04 N 00700
89980 USAL 05 S 00900
89980 USKA 09 S 00100
/*
//OUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=(7,2,CH,EQ,C'US'),
OVERLAY=(24:C'1',25:C'US ACCOUNTS'),HIT=NEXT),
IFTHEN=(WHEN=(7,2,CH,NE,C'US'),
OVERLAY=(24:C'1',25:C'NON US '),HIT=NEXT),
IFTHEN=(WHEN=(12,2,ZD,EQ,05),
OVERLAY=(24:C'1',50:C'CANCELLED '),HIT=NEXT),
IFTHEN=(WHEN=(12,2,ZD,NE,05),
OVERLAY=(24:C'1',50:C'NOT CANCELLED'))
SORT FIELDS=(26,15,CH,A,50,15,CH,A)
SUM FIELDS=(17,5,ZD,24,1,ZD)
OUTFIL FNAMES=OUT,
HEADER2=('ACCOUNT-STATUS CANCEL-STATUS COUNT TOTAL-AMOUNT',/,
'-------------- ------------- ----- ------------'),
OUTREC=(25,15,1X,50,15,1X,24,1,6X,17,5,5X)
Here is the output
Code:
ACCOUNT-STATUS CANCEL-STATUS COUNT TOTAL-AMOUNT
-------------- ------------- ----- ------------
NON US CANCELLED 1 00300
US ACCOUNTS CANCELLED 2 01400
US ACCOUNTS NOT CANCELLED 2 00800
Thank you, its working for the one condition as you mentioned. Do you know how i can extend it to other conditions as well in the same step?
Yes Sir, but i did not quite understand on using INREC for each combination and also for multiple lines. Could you please help with one condition? Sorry
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You use IFTHEN=(WHEN=INIT to initialise six extended fields which you generate on your record to zero. These are three counts, and three amounts.
You use three IFTHEN=(WHEN=(logicalexpression) with HIT=NEXT on the first two to identify where you need to place count/amount.
You use OUTFIL reporting features, NODETAIL and TRAILER1.
In the TRAILER1 you establish headings and your multiple detail lines, using the slash-operator (/) to get the multiple lines. You use TOT/TOTAL to get the total from your counts and amounts.
Get the first part working, with your SORTOUT going to SYSOUT, so that you can see it happening and see that you can then add up the counts and amounts.
Then do the OUTFIL. You will only get output at the end of processing, but you will get multiple lines with the total you want.
You use IFTHEN=(WHEN=INIT to initialise six extended fields which you generate on your record to zero. These are three counts, and three amounts.
You use three IFTHEN=(WHEN=(logicalexpression) with HIT=NEXT on the first two to identify where you need to place count/amount.
You use OUTFIL reporting features, NODETAIL and TRAILER1.
In the TRAILER1 you establish headings and your multiple detail lines, using the slash-operator (/) to get the multiple lines. You use TOT/TOTAL to get the total from your counts and amounts.
Get the first part working, with your SORTOUT going to SYSOUT, so that you can see it happening and see that you can then add up the counts and amounts.
Then do the OUTFIL. You will only get output at the end of processing, but you will get multiple lines with the total you want.
So for the INIT condition, i am doing this:
Code:
//SORT1 EXEC PGM=SORT
//SORTIN DD *
12345 USAL 05 S 00500
23879 BRPL 05 N 00300
89823 USGE 04 N 00700
89980 USAL 05 S 00900
89980 USKA 09 S 00100
/*
//OUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(25:C'000',50:C'000000',
75:C'000',100:C'000000',
125:C'000',150:C'000000')),
Could you please tell me what the logical expression for the next three IFTHEN would be, I thought there would be 6 IFTHEN conditions, one for each condition?
