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

sum and append the sum to each record in the group
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai
PostPosted: Tue May 24, 2011 8:41 pm
 Reply with quote
Hi,

I have a requirement to find the sum of accounts like below:

Input file looks like this:
act no- 1:3
amt - 4: 8
Name - 12:5

Code:
00100000052Anand
00100000061Kumar
00100000007Aruna
00200000004Anand
00300000002Prave
00300000001Veena

for each act no, we need to sum the amt's in this case 52+61+7 = 120 and write all the three records with 120 in end.

so my output file should be as follows:

Code:
00100000052Anand00000000120
00100000061Kumar00000000120
00100000007Aruna00000000120
00200000004Anand00000000004
00300000002Prave00000000003
00300000001Veena00000000003

Hence the same for act 002 the sum is 4 and the act 003 the sum is 2+1= 3.


Please can any one help in such a scenario?

topic edited to provide a[n almost] meaningful title
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue May 24, 2011 9:39 pm
 Reply with quote
Anand Kumar,
See if below works for you...

Code:

//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//SORTJNF1 DD *                                     
00100000052ANAND                                   
00200000004ANAND                                   
00100000061KUMAR                                   
00300000002PRAVE                                   
00300000001VEENA                                   
00100000007ARUNA                                   
/*                                                 
//SORTJNF2 DD *                                     
00100000052ANAND                                   
00200000004ANAND                                   
00100000061KUMAR                                   
00300000002PRAVE                                   
00300000001VEENA                                   
00100000007ARUNA                                   
/*                                                 
//SORTOUT  DD SYSOUT=*                             
//SYSIN DD *                                       
 JOINKEYS FILES=F1,FIELDS=(01,03,A)                 
 JOINKEYS FILES=F2,FIELDS=(01,03,A)                 
 REFORMAT FIELDS=(F1:01,16,                         
                  F2:81,08)                         
 OPTION COPY                                       
/*                                                 
//JNF2CNTL DD *                                     
 INREC OVERLAY=(81:04,08)                           
 SUM FIELDS=(81,8,ZD)                               
/*                                                 


OUTPUT
Code:

00100000052ANAND00000120
00100000061KUMAR00000120
00100000007ARUNA00000120
00200000004ANAND00000004
00300000002PRAVE00000003
00300000001VEENA00000003


Thanks,
Back to top
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai
PostPosted: Sun May 29, 2011 6:09 pm
 Reply with quote
Thank you..
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Mon May 30, 2011 5:58 am
 Reply with quote
I was thinking of two stage solution without joinkeys.. but this one looks better... Good one sqlcode1
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon May 30, 2011 6:53 am
 Reply with quote
Hello,

If the "real" account number is 10 bytes (instead of the 3 shown in the example) and there are 200 million records, duplicating the file may not be desirable. . .
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue May 31, 2011 8:15 pm
 Reply with quote
Escapa wrote:
I was thinking of two stage solution without joinkeys..


Without JOINKEYS,something like below might work as a single pass solution but I doubt the efficiency in this case... This was given by Kolusu on other board. In fact,I think the one with JOINKEYS was also provided by him icon_smile.gif
Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
$$$                                                                   
/*                                                                     
//         DD *                                                       
00100000052ANAND                                                       
00200000004ANAND                                                       
00100000061KUMAR                                                       
00300000002PRAVE                                                       
00300000001VEENA                                                       
00100000007ARUNA                                                       
/*                                                                     
//         DD *                                                       
$$$                                                                   
/*                                                                     
//         DD *                                                       
00100000052ANAND                                                       
00200000004ANAND                                                       
00100000061KUMAR                                                       
00300000002PRAVE                                                       
00300000001VEENA                                                       
00100000007ARUNA                                                       
/*                                                                     
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *
  INREC IFTHEN=(WHEN=INIT,                                             
                OVERLAY=(81:4,8,ZD)),                                 
        IFTHEN=(WHEN=GROUP,                                           
                BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(91:ID=1,SEQ=8)),       
        IFTHEN=(WHEN=(91,1,ZD,EQ,1),OVERLAY=(92:7C'0',C'1'))           
  SORT FIELDS=(1,3,CH,A,92,8,ZD,A),EQUALS                                   
  SUM FIELDS=(81,8,ZD)                                                 
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(91,1,ZD,EQ,1),PUSH=(81:81,8))       
  OUTFIL INCLUDE=(91,1,ZD,EQ,2),                                       
    BUILD=(1,16,X,81,8,ZD,TO=ZD,LENGTH=8)                             
/*                                                                     

OUTPUT
Code:

00100000052ANAND 00000120
00100000061KUMAR 00000120
00100000007ARUNA 00000120
00200000004ANAND 00000004
00300000002PRAVE 00000003
00300000001VEENA 00000003


Thanks,
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 How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us