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

Grouping as well as summing like records


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

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Wed Apr 16, 2008 10:25 pm
Reply with quote

Hi

I have the following requirement.

Sample Input:-
<Pos 1 to 5><pos 6 to 8> <Pos 9 to 11>
AAAAA 111 250
AAAAA 121 150
AAAAA 111 100
BBBBB 111 000
BBBBB 111 125
DDDDD 111 100
DDDDD 121 100

Expected Output:-

AAAAA 350 150 500
BBBBB 125 000 125
DDDDD 100 100 200

Where 350 is sum of all "111" belonging to "AAAAA"
150 is sum of all "121" belonging to "AAAAA"
500 is the Addition of the values 350 and 150

Can this be achieved using sort.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Apr 16, 2008 11:22 pm
Reply with quote

Do you only have '111' and '121' for the second field? If not, what do you want to do about other second field values (e.g. '333')?
Back to top
View user's profile Send private message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Thu Apr 17, 2008 12:12 pm
Reply with quote

Hi Frank

Actually there are 6 possible values that can come in second field. All are of 3 character numeric.

111
211
311 belongs to one group

121
131
141 belongs to second group
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Apr 17, 2008 10:25 pm
Reply with quote

raam_kumar,

The following DFSORT JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICEMAN                           
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                       
----+----1----+----2----+----3----+----4----+----5----
AAAAA 111 250                                         
AAAAA 121 150                                         
AAAAA 111 100                                         
AAAAA 311 100                                         
BBBBB 111 000                                         
BBBBB 111 125                                         
CCCCC 121 125                                         
CCCCC 131 125                                         
DDDDD 111 100                                         
DDDDD 121 100                                         
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  SORT FIELDS=(01,09,CH,A)                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(20:18C'0')),     
  IFTHEN=(WHEN=(7,3,CH,EQ,C'111'),OVERLAY=(20:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'211'),OVERLAY=(23:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'311'),OVERLAY=(26:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'121'),OVERLAY=(29:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'131'),OVERLAY=(32:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'141'),OVERLAY=(35:11,3)) 
  OUTFIL REMOVECC,NODETAIL,                           
  BUILD=(80X),                                       
  SECTIONS=(1,5,                                     
  TRAILER3=(1,5,X,TOT=(20,3,ZD,M10,LENGTH=8),X,       
                  TOT=(23,3,ZD,M10,LENGTH=8),X,       
                  TOT=(26,3,ZD,M10,LENGTH=8),X,       
                  TOT=(29,3,ZD,M10,LENGTH=8),X,       
                  TOT=(32,3,ZD,M10,LENGTH=8),X,       
                  TOT=(35,3,ZD,M10,LENGTH=8)))       
/*


The output from the above job is
Code:

AAAAA      350        0      100      150        0        0
BBBBB      125        0        0        0        0        0
CCCCC        0        0        0      125      125        0
DDDDD      100        0        0      100        0        0


Hope this helps...
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top