IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DFSORT report with multiple conditions


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Wed Jan 20, 2016 2:19 am
Reply with quote

Hi,

I have the following Account Number file:

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.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Wed Jan 20, 2016 5:04 am
Reply with quote

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.
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Wed Jan 20, 2016 5:50 am
Reply with quote

Bill Woodger wrote:
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.


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)                     
/*                                                           
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Wed Jan 20, 2016 3:35 pm
Reply with quote

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             
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Thu Jan 21, 2016 10:45 pm
Reply with quote

kranthikumarb wrote:
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             


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?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Fri Jan 22, 2016 12:06 am
Reply with quote

Did you see what I suggested earlier?
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Fri Jan 22, 2016 12:15 am
Reply with quote

Bill Woodger wrote:
Did you see what I suggested earlier?

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 icon_confused.gif
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Fri Jan 22, 2016 12:32 am
Reply with quote

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.
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Fri Jan 22, 2016 3:14 am
Reply with quote

Bill Woodger wrote:
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?
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 107
Location: India

PostPosted: Sat Jan 23, 2016 4:46 am
Reply with quote

Bill,

Sorry i am very good at SORT. I am not sure how to proceed further. Could you please help?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Creating a single-record file with CO... DFSORT/ICETOOL 8
No new posts Join lines with dfsort DFSORT/ICETOOL 5
No new posts Read 4MB message and split into multi... COBOL Programming 9
No new posts SARBCH REPORT DD writing error CA Products 1
No new posts Unload Report in ARCHIVE from CA-Disp... CA Products 0
Search our Forums:

Back to Top