|
View previous topic :: View next topic
|
| Author |
Message |
santhoshm
New User
Joined: 13 Jun 2007
Posts: 32
Location: chennai
|
|
|
|
SORT Matching records to one file and nonmatching to other
I have a similar kind of problem
I have two files with 3 variables VAR1, VAR2, VAR3
I have to compare 2 files with VAR1
if they match it should copy records from File1 into file3
and if they do not match it should copy records from file1 to file3 but the variable VAR3 should be copied from file2 |
|
| Back to top |
|
 |
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
You need to provide more information.
Show an example of the records in each input file and what you expect for output. If input file1 has duplicates within it, show that in the example. If input file2 has duplicates within it, show that in the example.
Give the starting position, length and format of all relevant fields in each file.
Give the RECFM and LRECL of each input file. |
|
| Back to top |
|
|
santhoshm
New User
Joined: 13 Jun 2007
Posts: 32
Location: chennai
|
|
|
|
Can you please help me. I've explained the problem below as well as the expected result.
I have two inputs.
Details of first input
Key field starts from position 7 to 18. File is sorted on the key.
| Code: |
***************************** Top of Data ******************************
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
0801012003000022 YXB001846A 0 5000000 8186.50
0801012002000065 YXB002256 0 5000000 1763.00
0801012000000128 YXB001625A 0 25000000 28051.42
0801012000000048 YXB001860 0 20000000 126.50
08010198000051 YXB001700 0 25000000 14200.15
0801012002000073 YXB001237C 0 10000000 2628.05
08010198000051 YXB001700 0 25000000 361.53
08010198000093 YXB001751 0 10000000 1777.95
0801012002000073 YXB001237C 0 10000000 60.50
0801012002000048 YXB002358 0 10000000 25102.77
**************************** Bottom of Data ****************************
|
Second input is also sorted on the same field as file 1. (Key positions 7 to 18)
| Code: |
sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
GRAMASA.INPUT2 Columns 00001 00072
d ===> Scroll ===> CSR
***************************** Top of Data ******************************
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
0801012003000022 YXB100000A 3453.00 2500000 7567.50
0801012002000065 YXB200000 242565.98 2423400 32343.00
0801012000000128 YXB300000A 3543.00 25675670 688851.42
0801012000000048 YXB348364 0 22342340 123326.50
08010198000051 YXB987750 23232232.00 25564570 18200.15
0801012002000049 YXB333333A 123424.00 5000000 25102.77
0801026406761 YYB300640A 5.98 0 752.92
08505007192001 YUB001489 543.00 5000000 4539.50
085050100501 YUB001664 6543.00 10000000 1350.00
08505082201 YUB001386E 194265.98 4000000 -4389.80
08505082201 YUB001383 294265.98 8000000 -8989.80
08505003G012 YUB300209D 2343.00 750000 935.00
08505098G028 YUB300209A 100.98 750000 852.50
0870602000030407 YXB300629A 2000.00 1000000 2588.73
**************************** Bottom of Data ****************************
|
Now the condition is
If the key field matches bet File 1 and 2,
then the record should be copied from file1 to file 3 (output file) and replace a few fields with values from file2. The fields that need to copied from File 1 is from Position 1 to 35 and again from 60 to end of the file.
From file 2, copy fields from position 36 to 59.
i.e. For the data that I've given above, only 5 records should be present on the o/p file. sample o/p for first matching record is given below
| Code: |
***************************** Top of Data ******************************
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
0801012003000022 YXB001846A 3453.00 2500000 8186.50
0801012002000065 YXB002256 242565.98 2423400 1763.00
0801012000000128 YXB001625A 3543.00 25675670 28051.42
0801012000000048 YXB001860 0 22342340 126.50
08010198000051 YXB001700 23232232.00 25564570 14200.15
|
Please note that only if the key is present on both the files, it should appear on the o/p.
Please let me know if this is unclear. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
Some questions on inconsistencies:
You say file1 is sorted on the key, but it doesn't appear to be. For example, the first few keys are:
| Code: |
2003000022
2002000065
2000000128
2000000048
98000051
2002000073
|
Those keys are NOT sorted in ascending or descending order, so why are you saying that file1 is sorted on the key? Likewise for file2.
It's not clear what you want to do when there are duplicate keys within file1 or within file2. For example, file1 has these duplicate records:
| Code: |
08010198000051 YXB001700 0 25000000 14200.15
08010198000051 YXB001700 0 25000000 361.53
|
File2 has a matching record:
| Code: |
08010198000051 YXB987750 23232232.00 25564570 18200.15
|
But you only show one of these file1 records in output (it appears to be the first instance from file1). Why not both?
You need to explain what exactly you want to do when there are duplicate records within file1, and when there are duplicate records within file2, for all matching and non-matching cases. Adding examples of the input and output records for these cases to your original example would help. |
|
| Back to top |
|
|
|
|
