It means all the three files can contain some records and it is possible that record with same key is available in other two file or just second file or none of other two.
Now as output I need to match based on Key(14-34) and get the below
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
can there be dups within a file?
you only want matched? whether it be 2 or 3 or only 3?
the process would be match 2 files,
matches would be output,
everything else dropped (or do you also want a discard file?)
the double matches from the 2 files would then be matched to the third
creating a 3 match file,
and everything else:
no match
2 macthes
would be dropped.
Correction in my first post is Key postions in all 3 files are 14 to 33 (20 character field)
File's will not have any duplicates. All the records available in 3 files should be in output file.
Case1: Key is available in all the files
File 1 has
Code:
000011111111199999999999999999999EF
File2 has
Code:
000044444444499999999999999999999EN
File3 has
Code:
000077777777799999999999999999999EA
Then output should be
First 13 postions of file 1 + 34 and 35 postions of file1 (Always 'EF') + First 13 position of file2 + 34 and 35 postions of file2 (Always 'EN') + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 3 files (As it is same)
File2
Dont have any record with key as 77777777777777777777
File3
Code:
000088888888877777777777777777777EA
Then the output should be
First 13 postions of file 1 + 34 and 35 postions of file1(Always 'EF') + First 13 position of file2(As there is no record so it should be 13 zeros) + 34 and 35 postions of file2(There is no record then also EN) + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 2 files (Either file1 or file2)
And similararly if record is there in File2 and File3 but not in File1. Or record is in File1 and File2 but not in File3
**
Case3: Key is available in only 1 files
Input1
Dont have any record with key as 33333333333333333333
Input2
Dont have any record with key as 33333333333333333333
Input3
Code:
000099999999933333333333333333333EA
Then the output should be
13 0's + EF + 13 0's + EN + First 13 position of file3 + 34 and 35 postions of file3(Always 'EA') + 14 to 33 from file3
Kolusu,
Unless I am missing something,not sure if $$ delimiter is needed here because each file can only have one value at 34th position. Also, I could be wrong here but that is your assumption as well.
Keeping the same in mind, wouldn't below method (on a similar lines of your provided solution) be suffice?
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
sqlcode1 wrote:
Kolusu,
Unless I am missing something,not sure if $$ delimiter is needed here because each file can only have one value at 34th position. Also, I could be wrong here but that is your assumption as well.
Keeping the same in mind, wouldn't below method (on a similar lines of your provided solution) be suffice?
Sqlcode,
I am not sure as to why you think that I assumed that each file has ONLY 1 value at pos 34. I used fixed constants on the output where OP specifically mentioned about it. I did NOT assume the values on the input and arrange the values based on it. I don't like to assume based on the sample data posted here. I posted a generic solution instead of relying on the sample data. The moment you have a different value other than EF on the first file , your solution will fail. May be the right question you need to ask is OP if your assumption is correct or not as I have no clue about the data.
Kolusu,
My statement/assumption was based on below comment from OP....
Quote:
First 13 postions of file 1 + 34 and 35 postions of file1 (Always 'EF') + First 13 position of file2 + 34 and 35 postions of file2 (Always 'EN') + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 3 files (As it is same)
However, I didn't mean to question your solution at all. I was just trying to seek your input on saving couple of IFTHENs.