Joined: 18 Jul 2007 Posts: 2150 Location: At my coffee table
It shouldn't matter, if you have DFSort, you have ICETOOL.
And DFSORT is called to sort the input data sets so the order shouldn't matter either.
Check out the two "Join fields from two files" in Smart DFSORT Tricks
And the size of fileds is :
key1 (3 chars) , col1,col2 col3 (4 chars )
key2(3 chars) , col4,col5 (4 chars)
File 1 can have duplicate key values , but File2 will have one key values .So takin key value from File1 corrsponding to that value from File2 we have to find out value of " col4 " and if not found in File2 then assign blank in output value.
In output file it will have all records as file1 with new column " col4 " derived on basis key
I suppose you expected me to read your mind to figure out that even though you showed blanks between the fields they weren't there, and that even though you didn't show duplicates they were there.
Do both files have RECFM=FB and LRECL=80?
Can input file2 have a key that isn't in input file1?
Can input file1 have a key that isn't in input file2?
Show me a good example of the input records in each file and what you expect for output with all of the possible variations. Since you say input file1 can have duplicate key values, show that in your example.
Sorry that i have not shown in my previous post about duplicate rows .
Yes ,both files have RECFM=FB and LRECL=80 .
Yes ,input file2 can have a key that isn't in input file1 .
Yes ,input file1 can have a key that isn't in input file2.
Basically the requirement is that file1 have 1 key field and 3 other column . file1 can have duplicate key values .
file2 have 1 key field and 2 other columns. All key values in file2 are unique.
Then ,output file will have all records from file1 with one more column from file2 .
we have to match the key from file1 to file2 , and get the column value from file2 and append to output file .if the key is not found in file2 then assign blank value in the output file for that new column .
It might also help if you display the records in T1 so you can see what they look like before they are spliced, and if you comment out the control statements in CTL3CNTL so you can see what the spliced records look like before OUTFIL removes the BB records and the ids.
I can give you some brief answers to your questions, but it may not help if you haven't spent the time to understand SPLICE.
1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?
WITHALL splices the base record (from file2) with each matching overlay record (from file1). WITH(5,15) and WITH(81,1) gives the fields from the overlay record to be spliced into the base record.
2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?
BB is an id code we're using to identify the records from file2. The file2 records are the base records.
3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?
VV is an id code we're using to identify the records from file1. The file1 records are the overlay records.
4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?
We're removing the records with an id of BB (records with keys that only appear in file2). We're keeping the records with an id of VV (records with keys that only appear in file1), and with an id of VB (records that appear in file2 and file1).
5. why we are using 81 here , whats its signinficance ?
We're adding the ids after the end of the records. The LRECL is 80, so the position after the end of the records is 81.
6. how it is working : step by step concept ?
See the explanations above and the referenced Smart DFSORT Trick.