|
View previous topic :: View next topic
|
| Author |
Message |
sumit agarwalla
New User
Joined: 13 Nov 2008
Posts: 17
Location: hyderabad
|
|
|
|
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 |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
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 |
|
|
sumit agarwalla
New User
Joined: 13 Nov 2008
Posts: 17
Location: hyderabad
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Just can't get the requestors anymore  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| I'll keep that in mind - Thanks Bill. |
|
| Back to top |
|
|
|
|
