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

Question in SYNCSORT
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Bharanidharan

New User


Joined: 20 Jun 2005
Posts: 86
Location: Chennai, India
PostPosted: Mon Jul 03, 2006 4:10 pm
 Reply with quote
My site uses Syncsort as primary sort product, so I would appreciate if you could give me a solution using syncsort.

Here is a sample I/P and O/P what I wish to achieve. I gave it a shot but couldn't get it through.
I/P: I have 3 fields - policy number, date and amount
Code:

polX 10/23/06 100
polY 10/25/06 200
polX 10/23/06 300
polX 09/23/06 400
polY 09/25/06 500

I need to sort this such that I have only one record for each policy in output, and the record being the one with latest date; in addition, if multiple records are present with latest date for a policy, I need the sum of amounts present in those records for that policy.
My output should be like this:
Code:

polX 10/23/06 400
polY 10/25/06 200

where the amount for polX is the addition of 100 and 300 present in records 1 & 3 in the I/P. Is it feasible to construct this in single step? Or is this feasible in multiple steps?

I am using SYNCSORT, but if this is not feasible, I will see if I could push DFSORT solution in for this. Thanks.
Back to top
View user's profile Send private message
vicky10001
Warnings : 1
Active User


Joined: 13 Jul 2005
Posts: 136
PostPosted: Mon Jul 03, 2006 6:24 pm
 Reply with quote
First use SORT after use SUM=NONE
Back to top
View user's profile Send private message
Jerry

New User


Joined: 16 Sep 2005
Posts: 42
PostPosted: Mon Jul 03, 2006 7:30 pm
 Reply with quote
Hi Bharanidharan,

I did it in two steps. There might be someway to do it in one step it self.

Code:

//SORT    EXEC PGM=SORT                                         
//SORTIN   DD  *                                                 
POLX 09/23/06 100                                               
POLY 10/25/06 200                                               
POLX 10/23/06 300                                               
POLX 09/23/06 400                                               
POLY 09/25/06 500                                               
/*                                                               
//SORTOUT     DD DSN=TEMP                       
//            DCB=(LRECL=80,BLKSIZE=80,RECFM=FB),               
//            UNIT=TEST,SPACE=(CYL,(70,100),RLSE),               
//            DISP=(NEW,CATLG,DELETE)                           
//SORTWK01 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK02 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK03 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK04 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK05 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SYSOUT   DD  SYSOUT=*                                         
//SYSOUD   DD  SYSOUT=*                                         
//SYSIN    DD  *                                               
   SORT FIELDS=(1,4,CH,A,6,8,CH,D)                             
//SORT1   EXEC PGM=SORT                                         
//SORTIN   DD  DSN=TEMP,DISP=SHR                       
//SORTOUT  DD  SYSOUT=*                                         
//SORTWK01 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK02 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK03 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK04 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SORTWK05 DD  UNIT=TEST,SPACE=(CYL,(20,5))                     
//SYSOUT   DD  SYSOUT=*                                         
//SYSOUD   DD  SYSOUT=*                                         
//SYSIN    DD  *                                               
   SORT FIELDS=(1,4,CH,A)                                       
   SUM FIELDS=(15,3,ZD)
Back to top
View user's profile Send private message
Jerry

New User


Joined: 16 Sep 2005
Posts: 42
PostPosted: Mon Jul 03, 2006 8:02 pm
 Reply with quote
Hi Bharanidharan,

You could do this in one step using SYNCTOOL as follows:
Code:

//STEP01   EXEC PGM=SYNCTOOL                                   
//SYSOUT   DD SYSOUT=*                                         
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1 DD *                                                     
POLX 09/10/06 100                                             
POLY 10/25/06 200                                             
POLX 10/23/06 300                                             
POLX 09/23/06 400                                             
POLY 09/25/06 500                                             
/*                                                             
//OUT1 DD SYSOUT=*                                             
//TEMP DD DSN=&T1,UNIT=TEST,SPACE=(CYL,(5,5)),DISP=(,PASS)     
//TOOLIN DD *                                                 
  SORT FROM(IN1) USING(CTL1)                                   
  SORT FROM(TEMP) TO(OUT1) USING(CTL2)                         
/*                                                             
//CTL1CNTL DD *                                               
   SORT FIELDS=(1,4,CH,A,6,8,CH,D)                             
   OUTFIL FNAMES=TEMP                                         
/*                                           
//CTL2CNTL DD *                               
   SORT FIELDS=(1,4,CH,A)                     
   SUM FIELDS=(15,3,ZD)                       
/*
Back to top
View user's profile Send private message
Bharanidharan

New User


Joined: 20 Jun 2005
Posts: 86
Location: Chennai, India
PostPosted: Wed Jul 05, 2006 10:35 am
 Reply with quote
Jerry,
Thanks for the quick replies. But your second sort step sums up the values present in all the records for a particular policy. I don't need the sum of all of them; I need the sum total of values present only in the records with latest dates for a policy. Could you please see the example in my previous posting again?
Here is the output from your steps for my input:
Code:

polX 10/23/06 800
polY 10/25/06 700

My output should be:
Code:

polX 10/23/06 400
polY 10/25/06 200

Please let me know if I am not clear.
Back to top
View user's profile Send private message
Jerry

New User


Joined: 16 Sep 2005
Posts: 42
PostPosted: Wed Jul 05, 2006 12:56 pm
 Reply with quote
Oops. Thanks for correcting me. You would have to include a sort step to remove the duplicates (using sum fields = none) after the first step to sort it in descending order.

Here is the part you would have to modify.

//TEMP DD DSN=&T1,UNIT=TEST,SPACE=(CYL,(5,5)),DISP=(,PASS)
//TEMP1 DD DSN=&T2,UNIT=TEST,SPACE=(CYL,(5,5)),DISP=(,PASS)
//TOOLIN DD *
SORT FROM(IN1) USING(CTL1)
SORT FROM(TEMP) USING(CTL2)
SORT FROM(TEMP1) TO(OUT1) USING(CTL3)
/*
//CTL1CNTL DD *
SORT FIELDS=(1,4,CH,A,6,8,CH,D)
OUTFIL FNAMES=TEMP
/*
//CTL2CNTL DD *
SORT FIELDS=(1,4,CH,A)
SUM FIELDS=NONE
OUTFIL FNAMES=TEMP1
/*
//CTL3CNTL DD *
SORT FIELDS=(1,4,CH,A)
SUM FIELDS=(15,3,ZD)
/*
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad
PostPosted: Wed Jul 05, 2006 12:57 pm
 Reply with quote
Hi,
Try this.
Code:


//STEP01   EXEC PGM=SYNCTOOL                   
//SYSOUT   DD SYSOUT=*                         
//TOOLMSG  DD SYSOUT=*                         
//DFSMSG   DD SYSOUT=*                         
//IN1 DD *                                     
POLX 10/23/06 100                             
POLY 10/25/06 200                             
POLX 10/23/06 300                             
POLX 09/23/06 400                             
POLY 09/25/06 500                             
/*                                             
//FINAL DD SYSOUT=*                           
//TEMP  DD DSN=xxxx.T1,                     
//         DISP=(NEW,CATLG,CATLG),             
//         UNIT=sysda,                         
//         SPACE=(TRK,(2,20),RLSE)             
//OUT1  DD DSN=xxx.T2,                     
//         DISP=(NEW,CATLG,CATLG),             
//         UNIT=sysda,                         
//         SPACE=(TRK,(2,20),RLSE)             
//TOOLIN DD *                                 
  SORT FROM(IN1) USING(CTL1)                   
  SORT FROM(TEMP) TO(OUT1) USING(CTL2)         
  SELECT FROM(OUT1) TO(FINAL) ON(1,4,CH) FIRST
/*                                             
//CTL1CNTL DD *                               
   SORT FIELDS=(1,4,CH,A,6,8,CH,D)             
   OUTFIL FNAMES=TEMP                         
/*                                             
//CTL2CNTL DD *                               
   SORT FIELDS=(1,13,CH,D)                     
   SUM FIELDS=(15,3,ZD)                       
/*                                             


Thank you
Krishy
Back to top
View user's profile Send private message
Bharanidharan

New User


Joined: 20 Jun 2005
Posts: 86
Location: Chennai, India
PostPosted: Wed Jul 05, 2006 2:26 pm
 Reply with quote
Jerry,
If you use FIELDS=NONE, you eliminate the duplicates even before you calculate the total. Your output gives:
Code:

polX 10/23/06 100
polY 10/25/06 200

Thanks for your reply Krishy. I tweaked Jerry's original solution a bit and accomplished using 2 controls instead of 3. Here it is:
Code:

//STEP01   EXEC PGM=SYNCTOOL                             
//SYSOUT   DD SYSOUT=*                                   
//TOOLMSG  DD SYSOUT=*                                   
//DFSMSG   DD SYSOUT=*                                   
//IN1 DD *                                               
POLX 10/23/06 100                                         
POLY 10/25/06 200                                         
POLX 10/23/06 300                                         
POLX 09/23/06 400                                         
POLY 09/25/06 500                                         
/*                                                       
//OUT1 DD SYSOUT=*                                       
//TEMP DD DSN=&T1,UNIT=TEST,SPACE=(CYL,(5,5)),DISP=(,PASS)
//TOOLIN DD *                                             
  SORT FROM(IN1) USING(CTL1)                             
  SORT FROM(TEMP) TO(OUT1) USING(CTL2)                   
/*                                                       
//CTL1CNTL DD *                                           
   SORT FIELDS=(1,4,CH,A,6,8,CH,D)                       
   SUM FIELDS=(15,3,ZD) 
   OUTFIL FNAMES=TEMP   
/*                       
//CTL2CNTL DD *         
   SORT FIELDS=(1,4,CH,A)
   SUM FIELDS=NONE       
/*                       


The O/P, which is what I need, is the same as that of Krishy's code. Thank you very much both for your time and efforts.
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Question for file manager IBM Tools 7
No new posts question for Pedro TSO/ISPF 2
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
No new posts question on Outrec and sort #Digvijay DFSORT/ICETOOL 20
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