View previous topic :: View next topic
Author
Message
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
I need to compare two files whose keys are of different length.
File 1: Key : First 12 chars. FB File
Code:
----+----1----+----2--
**********************
030931 9999-12-31
10634 9999-12-31
21912 9999-12-31
85675 9999-12-31
999128 9999-12-31
999835 9999-12-31
File 2 : Key : First 15 chars. VB File
Code:
----+----1----+----2----+----3
******************************
10634 100010634
21912 100021912
85675 100085675
999128 1000999128
Output: FB File
10634 100010634 9999-12-31
21912 100021912 9999-12-31
85675 100085675 9999-12-31
999128 1000999128 9999-12-31
Code i tried :
Code:
JOINKEYS FILES=F1,FIELDS=(1,12,A),
JOINKEYS FILES=F2,FIELDS=(4,15,A),
REFORMAT FIELDS=(F2:4,30,F1:13,10)
OPTION COPY
Error :
Code:
SYSIN :
JOINKEYS FILES=F1,FIELDS=(1,12,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
REFORMAT FIELDS=(F2:4,30,F1:13,10)
OPTION COPY
WER276B SYSDIAG= 4502103, 9473810, 9473810, 5326800
WER164B 93,032K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 12K BYTES RESERVE REQUESTED, 1,004K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER473A BOTH JOINKEYS STATEMENTS MUST HAVE CORRESPONDING KEY FIELDS OF EQUAL
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Can you let me know how to rectify this?
Thanks
Madhan
Back to top
expat Global Moderator Joined: 14 Mar 2007Posts: 8797 Location: Welsh Wales
You are comparing 12 bytes against 15 bytes.
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Quote:
I need to compare two files whose keys are of different length.
Yes. Is it not possible to do the same?
Back to top
daveporcelan Active Member Joined: 01 Dec 2006Posts: 792 Location: Pennsylvania
Quote:
Yes. Is it not possible to do the same?
Are we being punked here? Where is the camera?
When are the values EVER going to be the same?
Why not just use the first 12 bytes of the second file?
Back to top
dbzTHEdinosauer Global Moderator Joined: 20 Oct 2006Posts: 6966 Location: porcelain throne
MadanVS,
Code:
JOINKEYS FILES=F1,FIELDS=(1,12,A),SORTED <<<means start at col 1 for 12 char
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED <<<means start at col 4 for 15 char
besides,
you tell us
Quote:
code tried:
Code:
JOINKEYS FILES=F1,FIELDS=(1,12,A),
JOINKEYS FILES=F2,FIELDS=(4,15,A),
REFORMAT FIELDS=(F2:4,30,F1:13,10)
OPTION COPY
yet the sysin display for syncsort has the 'sorted' parm.
you don't pay attention to detail.
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Quote:
Why not just use the first 12 bytes of the second file?
File 1 will have key as : -------10634
File 2 will has key as : 10634-------
Quote:
yet the sysin display for syncsort has the 'sorted' parm.
I tried different options,
Yeap both files are sorted based on keys.
Back to top
daveporcelan Active Member Joined: 01 Dec 2006Posts: 792 Location: Pennsylvania
Quote:
File 1 will have key as : -------10634
File 2 will has key as : 10634-------
Those two fields do not look like a match to me.
This is going to need more 'creative' sort work than I care to provide.
Hint:
Step 1 - Use the Parse command to left justify the File 2 key into a 12 byte field.
Step 2 - use the Join keys on the two fields that ARE THE SAME LENGTH
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Hi daveporcelan
Quote:
Use the Parse command to left justify the File 2 key into a 12 byte field.
Is it possible to use INREC Parse along with Joinkeys?
Back to top
daveporcelan Active Member Joined: 01 Dec 2006Posts: 792 Location: Pennsylvania
As far as I know, no you can not.
That is why I suggested two steps.
You have an example of the parse in your other post.
I am sure you can work this out with some effort on your part.
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Hi
I reformatted one of my i/o file.
Now F1 is
Code:
----+----1----+----2----+----3----+----4-
***************************** Top of Data
106349999-12-31-00.00.00.000
219129999-12-31-00.00.00.000
856759999-12-31-00.00.00.000
0309319999-12-31-00.00.00.000
Key : 1,15 FB File
F2 is
Code:
----+----1----+----2----+----3-
***************************** T
10634 100010634
21912 100021912
85675 100085675
999128 1000999128
Key : 1,15 VB File
SortCard used:
Code:
JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
REFORMAT FIELDS=(F2:4,30,F1:13,10)
SORT FIELDS=COPY
END
Thou there are key matches I am getting empty file in my output.
Any idea?
Thanks
Madhan
Back to top
Thy New User Joined: 10 Jun 2005Posts: 6 Location: France
Hi,
If you want to keep only rows (keys) missing beetwen file1 and File2, you can user the UNPAIRED control statement:
JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
JOIN UNPAIRED,ONLY, F1
END
Expected results:
----+----1----+----2--
**********************
030931 9999-12-31
999835 9999-12-31
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Thanks for the reply Thy.
But i need only the matched records.
My output shud be as in the sortcard
JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
REFORMAT FIELDS=(F2:4,30,F1:16,10)
SORT FIELDS=COPY
END
Back to top
rakesh1155 New User Joined: 21 Jan 2009Posts: 84 Location: India
Hi Madan,
If you are accounting for 4 bytes of VB file, Shouldn't the JOINKEYS for F2 start from position 5 and not 4 ??
JOINKEYS FILES=F2,FIELDS=(4 ,15,A),SORTED
It should be:
Code:
JOINKEYS FILES=F2,FIELDS=(5,15,A),SORTED
-Thanks,
Rakesh.
Back to top
Thy New User Joined: 10 Jun 2005Posts: 6 Location: France
For VB file, you should start in pos 5 and not 4 (the 4 first pos are for RDW header).
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
Thanks Rakesh & Thy
Got it!!
Back to top
MadanVS New User Joined: 28 Dec 2010Posts: 25 Location: Chennai
By the way
I am using two steps to get the desired result:
Sort card in Step 1 :
Right justifying the KEY Part in file 1
Code:
SORT FIELDS=COPY
OUTREC BUILD=(1:3X,4:1,12,JFY=(SHIFT=RIGHT),16:13,26)
Sort card in step 2:
Code:
JOINKEYS FILES=F1,FIELDS=(1,15,A)
JOINKEYS FILES=F2,FIELDS=(5,15,A)
REFORMAT FIELDS=(F2:4,30,F1:16,10)
OPTION COPY
Is it possible to combine the sort cards and make it as a single step
?
Thanks
Madhan
Back to top
Please enable JavaScript!