View previous topic :: View next topic
|
Author |
Message |
vamsimul
New User
Joined: 07 Oct 2005 Posts: 6
|
|
|
|
Hi,
I've got a requirement where I've 2 files each one having records on some key. I'd need only unique records.
For eg:
File 1 has following data.
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
File 2 has following data.
03 MEXICO
05 Brazil
Afer I sort on the Country Name, I should get the output as below
01 INDIA
02 HOLLAND
04 NORWAY
05 Brazil
and I should write duplicate records in one more output as
02 HOLLAND
02 HOLLAND
Can any one help me in this. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Should Holland be Mexico? Do you mean you sort of the code, not the number?
Is the data already in order (either, or both files)? |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Quote: |
and I should write duplicate records in one more output as
02 HOLLAND
02 HOLLAND |
I hope this should be ??
03 MEXICO
03 MEXICO |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
and Is FILE1 and FILE2 similar in its layout ? |
|
Back to top |
|
|
vamsimul
New User
Joined: 07 Oct 2005 Posts: 6
|
|
|
|
yes HOLLAND has to be MEXICO.. it was a TYPO from my side. Thanks for the response. |
|
Back to top |
|
|
vamsimul
New User
Joined: 07 Oct 2005 Posts: 6
|
|
|
|
yes both the files are same in the format |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Try something like this
Code: |
//STEP0100 EXEC PGM=ICETOOL
//SYSOUT DD SYSOUT=*
//INPUT DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
// DD *
03 MEXICO
05 BRAZIL
//UNIQ DD SYSOUT=*
//DUPS DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(INPUT) TO(UNIQ) ON(1,2,CH) NODUPS DISCARD(DUPS) |
|
|
Back to top |
|
|
vamsimul
New User
Joined: 07 Oct 2005 Posts: 6
|
|
|
|
Is there any way we can do it by using EXEC PGM=SORT. 'COS in our shop we can't use ICETOOL. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Try this then
Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
//INB DD *
03 MEXICO
05 BRAZIL
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,2,A)
JOINKEYS F2=INB,FIELDS=(1,2,A)
JOIN UNPAIRED F1,F2,ONLY
REFORMAT FIELDS=(F1:1,10,F2:1,10)
OUTREC IFTHEN=(WHEN=INIT,BUILD=(1:1,21,JFY=(SHIFT=LEFT)))
OUTFIL FNAMES=OUT1,INCLUDE=(11,10,CH,NE,C' '),BUILD=(1,10,/11,10)
OUTFIL FNAMES=OUT2,SAVE
//* |
|
|
Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
Back to top |
|
|
vamsimul
New User
Joined: 07 Oct 2005 Posts: 6
|
|
|
|
Hi Pandora,
I tried executing your job, I'm getting the following issue: Since my input is matched according to 12 bytes I've used length as 12 for keys.
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 2D2E6, MODEL 2817 724
SYSIN :
OPTION COPY
JOINKEYS F1=IN1,FILEDS=(1,12,A)
*
JOINKEYS F2=IN2,FILEDS=(1,12,A)
*
JOIN UNPAIRED F1,F2 ONLY
REFORMAT FIELDS=(F1:1,12,F2:1,12)
OUTREC IFTHEN=(WHEN=INIT,BUILD=(1:1,24,JFY=(SHIFT=LEFT)))
OUTFIL FNAMES=OUT1,INCLUDE=(13,12,CH,EQ,C' '), BUILD=(1,12,/13,12)
OUTFIL FNAMES=OUT2,SAVE
*
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A OUTFIL STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
You have SYNCSORT product
Also the solution from suresh wont help you as well
As you wanted the Unique keys in one file and duplicates in another |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
XSUM, on the SUM or using DUPKEYS (if you are up-to-date enough) would be correct for SyncSort. |
|
Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
I thought for a minute that I was right... But again...
Vamsi wants all the unique records in 1 file and all duplicates to another file.
if SUM=NONE,XSUM is being used, The First duplicate record will send to the Unique file.
I guess, Bill will have some other solution |
|
Back to top |
|
|
JAYACHANDRAN THAMPY
New User
Joined: 06 Jun 2006 Posts: 8
|
|
|
|
If you have Syncsort V1.4, then the below syncsort jcl provides you the result using DUPKEYS ALLDUPS options.
Quote: |
//STEP010 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
// DD *
03 MEXICO
05 BRAZIL
//SYSOUT DD SYSOUT=*
//SORTXDUP DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,9,CH,A)
DUPKEYS ALLDUPS,XDUP
//* |
SORTOUT output have the duplicate records and the non duplicate records are written to SORTXDUP.
SORTOUT Output
03 MEXICO
03 MEXICO
SORTXDUP output
01 INDIA
02 HOLLAND
04 NORWAY
05 BRAZIL |
|
Back to top |
|
|
|