Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DFSORT report with multiple conditions

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 103
Location: India

PostPosted: Wed Jan 20, 2016 2:19 am    Post subject: DFSORT report with multiple conditions
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Jan 20, 2016 5:04 am    Post subject: Reply to: DFSORT report with multiple conditions
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: 103
Location: India

PostPosted: Wed Jan 20, 2016 5:50 am    Post subject: Re: Reply to: DFSORT report with multiple conditions
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: 114
Location: Hyderabad

PostPosted: Wed Jan 20, 2016 3:35 pm    Post subject:
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: 103
Location: India

PostPosted: Thu Jan 21, 2016 10:45 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jan 22, 2016 12:06 am    Post subject: Reply to: DFSORT report with multiple conditions
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: 103
Location: India

PostPosted: Fri Jan 22, 2016 12:15 am    Post subject: Re: Reply to: DFSORT report with multiple conditions
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jan 22, 2016 12:32 am    Post subject: Reply to: DFSORT report with multiple conditions
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: 103
Location: India

PostPosted: Fri Jan 22, 2016 3:14 am    Post subject: Re: Reply to: DFSORT report with multiple conditions
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: 103
Location: India

PostPosted: Sat Jan 23, 2016 4:46 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts Detailed Transaction History report f... Kyle Carroll CICS 5 Tue Oct 18, 2016 12:42 am
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us