Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Sum amount by grouping on a field and insert header into o/p

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

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Wed Feb 25, 2009 10:19 am    Post subject: Sum amount by grouping on a field and insert header into o/p
Reply with quote

Hi,

I have a variable block format dataset. The data in it looks like this

Code:

----+----1----+----2----+----3----+----4
2000 2008-22-06 799986 BANK1  abcd123
2000 2008-22-06 799987 BANK1  abc
2000 2008-22-06 799986 BANK1  1321
3000 2008-22-06 799333 BANK2  abcd12345
3000 2008-22-06 799333 BANK2 
3000 2008-22-06 799333 BANK2 
1000 2008-22-06 799333 BANK3 
1000 2008-22-06 799333 BANK3 
1000 2008-22-06 799333 BANK3 
1000 2008-22-06 799333 BANK3 


The output should contian the summed up value for amount field for a particular bank. It should contain a header for a bank with sum of the amount and all the records contributiong for that sum should be present below. The expected output is

Code:

----+----1----+----2----+----3
HEADER 6000 2008-22-06 799986 BANK1

DETAIL 2000 2008-22-06 799986 BANK1 
DETAIL 2000 2008-22-06 799987 BANK1 
DETAIL 2000 2008-22-06 799986 BANK1

HEADER 9000 2008-22-06 799333 BANK2
 
DETAIL 3000 2008-22-06 799333 BANK2 
DETAIL 3000 2008-22-06 799333 BANK2 
DETAIL 3000 2008-22-06 799333 BANK2

HEADER 4000 2008-22-06 799333 BANK3
 
DETAIL 1000 2008-22-06 799333 BANK3 
DETAIL 1000 2008-22-06 799333 BANK3 
DETAIL 1000 2008-22-06 799333 BANK3 
DETAIL 1000 2008-22-06 799333 BANK3


I tried a lot... but no result. Can someone tell me how to do it using DFSORT/ICETOOL?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 25, 2009 11:40 am    Post subject:
Reply with quote

Hello,

It will help if you post what you have done so far.

People here should be able to point out problems or offer alternatives.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Wed Feb 25, 2009 11:51 am    Post subject:
Reply with quote

Couldn't do this using ICETOOL/SORT. SO I am trying using ICEMAN

As a first step i tried using ICEMAN with the following sort card

Code:

//SYSIN DD *           
 OPTION VLSHRT         
 SORT FIELDS=(31,5,CH,A)
 SUM FIELDS=(5,4,ZD)   
/*                     


But in the sum value of the o/p file, i am getting some junk values in the last character. Merge a key to all these records "header" or at the end 01,02,03 .

and step I tried to merge the output of setp one with original file using a key that was put in step one.
This is just an idea i have. And I am working on it.
Folks, Please help If you have any ideas.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Wed Feb 25, 2009 1:53 pm    Post subject:
Reply with quote

I was able to do it except that there is a small problem in the output field

Code:

HEADER600{ 2008-22-06 799986 BANK1
DETAIL2000 2008-22-06 799986 BANK1
DETAIL2000 2008-22-06 799987 BANK1
DETAIL2000 2008-22-06 799986 BANK1
HEADER900{ 2008-22-06 799333 BANK2
DETAIL3000 2008-22-06 799333 BANK2
DETAIL3000 2008-22-06 799333 BANK2
DETAIL3000 2008-22-06 799333 BANK2
HEADER400{ 2008-22-06 799333 BANK3
DETAIL1000 2008-22-06 799333 BANK3
DETAIL1000 2008-22-06 799333 BANK3
DETAIL1000 2008-22-06 799333 BANK3
DETAIL1000 2008-22-06 799333 BANK3


the last digiti in the total amount field of header is going wrong. Can someone help?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2287
Location: @my desk

PostPosted: Wed Feb 25, 2009 2:27 pm    Post subject:
Reply with quote

kranthikumarb,

Can you post the control cards and SYSOUT messages here using "Code" tags.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Wed Feb 25, 2009 3:44 pm    Post subject:
Reply with quote

Hi,
I prepared the JCL and is working fine. Here is the JCL. I think the same can be done in 2-3 steps.

here it is

Code:

//* STEP TO ADD THE AMOUNT FIELDS BASED ON BANK AND ADD KEYWORD HEADER
//STEP1 EXEC PGM=SORT                                                 
//SYSOUT DD SYSOUT=A                                                 
//SYSPRINT DD SYSOUT=A                                               
//SORTIN DD DSN=INPPUT,DISP=SHR                           
//SORTOUT DD DSN=OUTPUT1,DISP=OLD                       
//SYSIN DD *                                                         
   OPTION ZDPRINT                                                     
   OPTION VLSHRT                                                     
   SORT FIELDS=(28,5,CH,A)                                           
   SUM FIELDS=(5,4,ZD)                                               
   OUTREC FIELDS=(1,4,C'HEADER',5,4,1X,10,10,1X,21,6,1X,28,5)         
/*                                                                   
//* STEP TO APPEND THE KEYWORD "DETAIL" TO EACH RECORD               
//STEP2 EXEC PGM=SORT                                                 
//SYSOUT DD SYSOUT=A                                                 
//SYSPRINT DD SYSOUT=A                                               
//SORTIN DD DSN=INPUT,DISP=SHR                           
//SORTOUT DD DSN=INPUT1, DISP=OLD                         
//SYSIN DD *                                                         
   OPTION VLSHRT                                                     
   SORT FIELDS=COPY                                                   
   OUTREC FIELDS=(1,4,C'DETAIL',5,4,1X,10,10,1X,21,6,1X,28,5)         
/*   
//* STEP TO APPEND MERGE RECORDS BASED ON THE BANKFIELD
//STEP3 EXEC PGM=SORT                         
//SYSOUT DD SYSOUT=A                           
//SYSPRINT DD SYSOUT=A                         
//SORTIN01 DD DSN= INPUT1,DISP=SHR
//SORTIN02 DD DSN= OUTPUT1,DISP=SHR
//SORTOUT DD DSN= OUTPUT2,DISP=OLD
//SYSIN DD *                                   
   OPTION VLSHRT                               
   MERGE FIELDS=(34,5,CH,A)                   
/*     
//* STEP TO SORT THE AMOUNT FIELDS ( PUTTING HEADER AT THE TOP OF EACH GROUP)                                       
//STEP4 EXEC PGM=SORT                         
//SYSOUT DD SYSOUT=A                           
//SYSPRINT DD SYSOUT=A                         
//SORTIN  DD DSN=OUTPUT2,DISP=SHR
//SORTOUT DD DSN=OUTPUT3,DISP=OLD
//SYSIN DD *                                   
 OPTION VLSHRT                                 
 SORT FIELDS=(34,5,CH,A,11,4,BI,D)             
/*
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 25, 2009 9:18 pm    Post subject:
Reply with quote

Hello,

Quote:
the last digiti in the total amount field of header is going wrong.
There is nothing wrong - you specified zoned-decimal and that includes a sign. This "600{" = "6000".
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Feb 25, 2009 10:46 pm    Post subject:
Reply with quote

kranthikumarb,

There are a couple of problems with the code you have shown. What happens if the amount when summed exceeds 9999? Since you only 4 bytes to accomdate the amount, would you truncate? Also you are only creating a 38 byte output file. Is that you really want? Based on the control cards you used here is a 2 step process which would give you the desired results.

Code:

//* CREATE 2 FILES
//* HDR = SUM OF AMOUNT FIELDS BASED ON BANK AND ADD KEYWORD HEADER
//* DTL = APPEND THE KEYWORD "DETAIL" TO EACH RECORD   
//*
//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=INPPUT,DISP=SHR
//HDR      DD DSN=&&H1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE) 
//DTL      DD DSN=&&D1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//SYSIN    DD *                                           
  OPTION VLSHRT                                           
  SORT FIELDS=(28,5,CH,A)                                 
  OUTFIL FNAMES=HDR,REMOVECC,NODETAIL,BUILD=(1,4,34X),   
  SECTIONS=(28,5,                                         
  TRAILER3=(C'HEADER',                                   
            TOT=(5,4,ZD,M11,LENGTH=4),                   
            X,10,10,1X,21,6,1X,28,5))                     
  OUTFIL FNAMES=DTL,                                     
  BUILD=(1,4,C'DETAIL',5,4,1X,10,10,1X,21,6,1X,28,5)     
/*                                                         
//* SORT THE ABOVE 2 FILES SO AS TO GROUP THE SAME BANK RECORDS TOGETHER
//*
//STEP0200 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=&&H1,DISP=SHR                             
//         DD DSN=&&D1,DISP=SHR                             
//SORTOUT  DD SYSOUT=*                                     
  OPTION VLSHRT,EQUALS                                     
  SORT FIELDS=(34,5,CH,A)                                   
/*
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Thu Feb 26, 2009 9:12 am    Post subject:
Reply with quote

Hi skolusu,

Thanks for your reply. But the one I poseted is a trial version kinda thing. I tested with some sample input just. I've taken care about the amount fields ( 4 chars....) in the original JCL.

Never the less I've taken your code as well, I'll implement the best logic.
Once again many thanks.

Regards,
Kranthi
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
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us