|
View previous topic :: View next topic
|
| Author |
Message |
sreerocks
New User
Joined: 18 Jun 2008
Posts: 9
Location: chennai
|
|
|
|
I was trying to compare file F1 and F2 to get all the records in F1 which is not there in F2.
PLease see the code below.
| Code: |
JOINKEYS FILES=F1,FIELDS=(1,6,A)
JOINKEYS FILES=F2,FIELDS=(1,6,A)
JOIN UNPAIRED F1,ONLY
REFORMAT FIELDS=(F1:1,6)
SORT FIELDS=COPY |
but I am getting records which are matching and some other junk values along with the correct values.
I am using sorted files.
Could someone help me out.
Thanks
sree |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
sreerocks,
I dont see anything wrong with the control cards you have used. Make sure your file postions are correct. Can you post some sample records with key values and also the output you are getting, using the "Code" tag. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Also the input/output file attributes... RECFM/LRECL etc.. |
|
| Back to top |
|
|
sreerocks
New User
Joined: 18 Jun 2008
Posts: 9
Location: chennai
|
|
|
|
Hi,
I got it..
I missed one comma after the command UNPAIRED.
Now it is coming correct.
It should be
JOIN UNPAIRED,F1,ONLY
Thanks for the help.. |
|
| Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India
|
|
|
|
Hi,
I too have almost similar type of requirement.
I have two files F1 and F2... Both have different data and LRECL is 80 and FB mode. I want compare two fields from file F1 say 3rd variable and 5th variable in File F1 with 6th variable and 8 th variable in F2. If match then i want file f1 record to one file and unmatching record to another file. So my output should be two file one with match and another with unmatch. I want file F1 records only in the bothe files. I don't want any data from file 2. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Sasikumar,
If I understand correctly,you are trying to write matched records from file-1 into OUT1 and unmatched records from file-1 into OUT2.
What are the positions and lengths of the key fields in both the files? |
|
| Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India
|
|
|
|
| Yes I have a variable in File 1 at 7th byte and length is 9 and another variable at 20th byte and length is 12, I want these two files to be matched with file 2. In file 2 the first variable is at 1st byte and length is 9 and second variable is at 15 position and lenght is 12 bytes. If file 1 variables matches with File 2 variables then i want file 1 full record (LRecl is 80) to one file and unmatched records to another file. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Sasikumar,
I dont have access to mainframe now. But I think the below Syncsort JOIN UNPAIRED application would do the job for you. Try this and post back if you face any issues.
| Code: |
//STEP1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//MATCH DD SYSOUT=*
//NOMATCH DD SYSOUT=*
//SORTJNF1 DD DSN=file-1
//SORTJNF2 DD DSN=file-2
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(7,9,A,20,12,A)
JOINKEYS FILES=F2,FIELDS=(1,9,A,15,12,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,F2:1,1)
SORT FIELDS=COPY
OUTFIL FNAMES=MATCH,INCLUDE=(81,1,CH,NE,C' '),BUILD=(1,80)
OUTFIL FNAMES=NOMATCH,SAVE,BUILD=(1,80)
/*
|
|
|
| Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India
|
|
|
|
It works as expected. But I have few questions:
1) INCLUDE=(81,1,CH,NE,C' '),BUILD=(1,80), why we arr using this when writing match records??
2) SAVE,BUILD=(1,80) , why we are using this when writing unmatch file. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Sasikumar,
You're welcome. Please dont do double-posting.
This will write all the records from file-1 into output (matching + non-matching).
| Code: |
| REFORMAT FIELDS=(F1:1,80,F2:1,1) |
Here I have taken the first byte of the file-2 key as an indicator for further processing. This byte will have a value not = ' ' for every match. For non-matching records, the default fill-byte is ' ' which gets routed to OUT2 via SAVE which captures the remaining records.
You can refer the Syncsort manual to get to know more about this. If you don't have one, contact Syncsort support team or send a PM to Alissa. |
|
| Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
| sreerocks wrote: |
I am using sorted files.
|
in that case you can add SORTED to avoid sorting:
| Code: |
JOINKEYS FILES=F1,FIELDS=(1,6,A),SORTED
JOINKEYS FILES=F2,FIELDS=(1,6,A),SORTED |
hth |
|
| Back to top |
|
|
|
|
