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
 

 

Sort help required

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sumit agarwalla

New User


Joined: 13 Nov 2008
Posts: 17
Location: hyderabad

PostPosted: Fri Mar 22, 2013 9:20 am    Post subject: Sort help required
Reply with quote

Hi,

I have a file with Accounts linked with Customer ID's. One Account can be linked to multiple Customers. I want to get the Accounts which are linked to more than one customer. I want to do it thru SORT. I have syncsort installed in my shop. I am stuck here and need some help. I have the below input and output file. Could you please provide me some direction if this can be done thru sort?

Code:


Input

Account Customer ID
12345    1111
12345    2222
45678    3333
34567    4444
34567    5555



Code:


Output

Account Customer ID
12345    1111
12345    2222
34567    4444
34567    5555


[/code]
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Mar 22, 2013 10:52 am    Post subject:
Reply with quote

Try:
Code:
//STEP010  EXEC PGM=ICETOOL                       
//TOOLMSG   DD SYSOUT=*                           
//DFSMSG    DD SYSOUT=*                           
//INPUT    DD *                                   
12345    1111                                     
12345    2222                                     
45678    3333                                     
34567    4444                                     
34567    5555                                     
//OUTPUT   DD  SYSOUT=*                           
//TOOLIN DD *                                     
  SELECT FROM(INPUT) TO(OUTPUT) ON(1,5,CH) ALLDUPS
/*                                               
Back to top
View user's profile Send private message
sumit agarwalla

New User


Joined: 13 Nov 2008
Posts: 17
Location: hyderabad

PostPosted: Fri Mar 22, 2013 4:17 pm    Post subject: Reply to: Sort help required
Reply with quote

Thanks Anuj.

I tried your code and it is working fine. I missed one of the inputs , my fault. i added two more rows in the input. The ALLDUPS statement will include the last two records also (ACCNO- 65789). I dont want this as it is linked to one customer only. Is this possible thru sort? Sorry for the confusion.

Code:


Input

Account Customer ID
12345    1111
12345    2222
45678    3333
34567    4444
34567    5555
65789    6666
65789    6666



Code:


Output

Account Customer ID
12345    1111
12345    2222
34567    4444
34567    5555

Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Mar 22, 2013 4:55 pm    Post subject:
Reply with quote

Try:
Code:
//STEP010  EXEC PGM=ICETOOL                                   
//TOOLMSG   DD SYSOUT=*                                       
//DFSMSG    DD SYSOUT=*                                       
//INPUT    DD *                                               
12345    1111                                                 
12345    2222                                                 
45678    3333                                                 
34567    4444                                                 
34567    5555                                                 
65789    6666                                                 
65789    6666                                                 
//OUTPUT   DD  SYSOUT=*                                       
//TOOLIN DD *                                                 
  SELECT FROM(INPUT) TO(OUTPUT) USING(CTL1) ON(1,5,CH) ALLDUPS
/*                                                           
//CTL1CNTL DD *                                               
  SORT FIELDS=(10,4,CH,A)                                     
  SUM FIELDS=NONE                                             
/*                                                           
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Mar 22, 2013 5:33 pm    Post subject:
Reply with quote

Anuj,

I would suggest that BOTH of the two fields are sorted with SUM FIELDS=NONE rather than just the second field.

With the following
Code:

//INPUT    DD *   
12345    1111     
12345    2222     
45678    3333     
34567    4444     
34567    5555     
65789    1111     
65789    6666     
65789    6666     

The output was
Code:

12345    1111
12345    2222
34567    4444
34567    5555

Where I suspect the required output should have been
Code:

12345    1111
12345    2222
34567    4444
34567    5555
65789    1111
65789    6666
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Mar 22, 2013 5:39 pm    Post subject:
Reply with quote

Possibly expat, though it's tough to keep track on a moving requirement. Intially, for test I had
Code:
//CTL1CNTL DD *         
  SORT FIELDS=(1,13,CH,A)
  SUM FIELDS=NONE       
/*                       
but later, looking at test data from OP, I've posted what we see there.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Mar 22, 2013 5:47 pm    Post subject:
Reply with quote

Just can't get the requestors anymore icon_biggrin.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sat Mar 23, 2013 5:48 am    Post subject: Reply to: Sort help required
Reply with quote

Indeed, expat is correct. The SORT has to be on both elements (or 1,13 will also work, as each record has the same amount of blanks in between). The SUM FIELDS=NONE gets rid of multiple values for the subkey. The SELECT and ALLDUPS, working only on the major key, will output all subkeys (which are by now individual records per subkey), as long as there is more than one subkey.

You have to be careful including SORT in a SELECT, as the key-fields specified in any ONs must be specified first in the SORT statement. Otherwise, key values for the ON fields may not be contiguous and the results may not be what you expect.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Mar 23, 2013 2:54 pm    Post subject:
Reply with quote

I'll keep that in mind - Thanks Bill.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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