|
View previous topic :: View next topic
|
| Author |
Message |
MadanVS
New User
Joined: 28 Dec 2010
Posts: 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 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| You are comparing 12 bytes against 15 bytes. |
|
| Back to top |
|
|
MadanVS
New User
Joined: 28 Dec 2010
Posts: 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 2006
Posts: 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 2006
Posts: 6965
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 2010
Posts: 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 2006
Posts: 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 2010
Posts: 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 2006
Posts: 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 2010
Posts: 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
.jpg)
Joined: 10 Jun 2005
Posts: 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 2010
Posts: 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 2009
Posts: 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 2005
Posts: 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 2010
Posts: 25
Location: Chennai
|
|
|
|
Thanks Rakesh & Thy
Got it!! |
|
| Back to top |
|
|
MadanVS
New User
Joined: 28 Dec 2010
Posts: 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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
