Lookup not returning the right row
Posted: Thu Mar 02, 2006 7:04 am
Hi,
I am facing a strange problem when using OCI lookups. I am having an oracle table which has all the makes and models of cars. The table has three columns acutalmake, acutalmodel, incorrectmodel
For eg
MAZDA MX-3 MX3
MAZDA MX-5 MX5
MAZDA MX-6 MX6
MAZDA RX-7 RX-7
MAZDA RX-8 RX8
We have a file that has to be cleansed with this lookup. The file has the following fields
URN,make,model
So when the file comes in, we will first check if the record exists for the right make and model in the table. If it is there, then don't do anything. If the model is not there, check in the incorrect model field. If the model in the file matches the incorrect model, then change the model in the file to the actual model from the table. For eg if the file has the record format like
1234,MAZDA,MX3
First we check whether MAZDA,MX3 is the right combination.Since MX3 is not a valid model, we check in the incorrect model field. Now MX3 is matched with that field. So the model in the file will be updated with the actual model which is MX-3.
Now the problem is, the field in the file is getting updated, but in a slightly different way.When I have records like
1234,MAZDA,MX3
1234,MAZDA,MX5
1234,MAZDA,MX6
1234,MAZDA,RX7
1234,MAZDA,RX8
The records are getting populated as
1234,MAZDA,MX-3
1234,MAZDA,MX-3
1234,MAZDA,MX-3
1234,MAZDA,RX-7
1234,MAZDA,RX-7
ie MX5 is not updated as MX-5 but it is getting updated as MX-3.Can someone tell me why this is happening.
I am facing a strange problem when using OCI lookups. I am having an oracle table which has all the makes and models of cars. The table has three columns acutalmake, acutalmodel, incorrectmodel
For eg
MAZDA MX-3 MX3
MAZDA MX-5 MX5
MAZDA MX-6 MX6
MAZDA RX-7 RX-7
MAZDA RX-8 RX8
We have a file that has to be cleansed with this lookup. The file has the following fields
URN,make,model
So when the file comes in, we will first check if the record exists for the right make and model in the table. If it is there, then don't do anything. If the model is not there, check in the incorrect model field. If the model in the file matches the incorrect model, then change the model in the file to the actual model from the table. For eg if the file has the record format like
1234,MAZDA,MX3
First we check whether MAZDA,MX3 is the right combination.Since MX3 is not a valid model, we check in the incorrect model field. Now MX3 is matched with that field. So the model in the file will be updated with the actual model which is MX-3.
Now the problem is, the field in the file is getting updated, but in a slightly different way.When I have records like
1234,MAZDA,MX3
1234,MAZDA,MX5
1234,MAZDA,MX6
1234,MAZDA,RX7
1234,MAZDA,RX8
The records are getting populated as
1234,MAZDA,MX-3
1234,MAZDA,MX-3
1234,MAZDA,MX-3
1234,MAZDA,RX-7
1234,MAZDA,RX-7
ie MX5 is not updated as MX-5 but it is getting updated as MX-3.Can someone tell me why this is happening.