Hi,
I was going through this technique in Smart DFSORT Tricks.
Consider this case where File1 has duplicates and File2 doesn't.
Each file has a name and amount key.
File1 has the amount field in positions 1-4 and the name field in positions 6-13.
File2 has the amount field in positions 12-15 and the name field in positions 3-10.
Here are the FB input records:
A Karen 0003
D Holly 0008
X Carrie 0005
R Vicky 0003
L Mary 1023
We want to keep all of the records in File1 that have a match in File2 on the amount and name fields. But in this
case, we have duplicates in File1. The 0003 Vicky, 0005 Carrie and 0008 Holly records in File1 have a match in
File2, so we want to keep the two 0003 Vicky records, three 0005 Carrie records, and one 0008 Holly record from
File1. The 0007 Vicky, 0015 Frank, 0006 Carrie and 0103 David records in File1 do not have a match in File2, so
we don't want to keep those records.
I would like to know, is it possible to retain the unpaired rows from File 1 as well in the same sort step? With a default value of mine, when it is not paired?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
I would like to know, is it possible to retain the unpaired rows from File 1 as well in the same sort step? With a default value of mine, when it is not paired?
It's not clear what you mean by this. What exactly do you want for output? How many output files do you want?
Please show an example of the records you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
A Karen 0003
D Holly 0008
X Carrie 0005
R Vicky 0003
L Mary 1023
Compare From Input 1 (1,4) with Input 2 (10,4)
On match, field 1 of LRECL 80 with field (1,1) from file in 81st position.
On No match, field 1 of LRECL 80 with field (1,1) as Z in 81st position.
Output
Code:
0003 Vicky O1 R
0007 Vicky O2 R
0003 Vicky O3 R
0015 Frank O1 Z
0005 Carrie O1 X
0005 Carrie O2 X
0005 Carrie O3 X
0006 Carrie O4 X
0008 Holly O1 D
0103 David O1 Z
0103 David O2 Z
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
Compare From Input 1 (1,4) with Input 2 (10,4)
On match, field 1 of LRECL 80 with field (1,1) from file in 81st position.
On No match, field 1 of LRECL 80 with field (1,1) as Z in 81st position.
This would NOT give you the output you show. For example,
1-4 (0003) in the first and third file1 records match 10-4 (0003) in both File2 records.
1-4 (0007) in the second file1 record does not match 10-4 (0007) in any File2 record.
So how do you get this for output:
Code:
0003 Vicky O1 R
0007 Vicky O2 R
0003 Vicky O3 R
I think you really want to compare on the name field rather than the number field, but you need to explain exactly what you want to do with a good example of input and output, and a corresponding descriptions of the rules for getting from input to output, before I can help you.
Please take the time to state your requirement clearly and accurately.
1234567890123
-------------
A Karen 0003
D Holly 0008
X Carrie 0005
R Vicky 0013
L Mary 1023
Compare From Input 1 (1,4) with Input 2 (10,4)
On match, file1 of LRECL 14 + file2's field (1,1) in 15th position.
On No match, file1 of LRECL 14 + file2's field (1,1) as Z in 15st position.
This is what, I'm trying to achieve.
I'm aware that the output other than the above two can be achieved through DFSORT Join keys.
Trying to get these two records as well.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I just got to work. I suspect I'm in a different time zone than you are.
Given that you're in such a hurry to get an answer, you ought to take more time to state your requirement correctly. Your expected output does NOT make sense. You show R as the last output character for the 0103 records, but it should be Z since there's no 0103 record in file2.
Assuming your expected output should really be as follows:
