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
 

 

Question in SYNCSORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Question in SYNCSORT
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Re: Question in SYNCSORT
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    Post subject:
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: 182
Location: hyderabad

PostPosted: Wed Jul 05, 2006 12:57 pm    Post subject:
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    Post subject: Re: Question in SYNCSORT
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts ICETOOL/SYNCSORT - SYSPRINT output nartcr DFSORT/ICETOOL 17 Wed Oct 05, 2016 10:46 pm


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