Lookup not returning the right row

dprasanth · Post by **dprasanth** » 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.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 02, 2006 7:06 am

What key are you using for the lookup? if its only 1234 - note that all of these have 1234 - then the first one found is returned. You need more specific information for this kind of lookup.

dprasanth · Post by **dprasanth** » Thu Mar 02, 2006 7:07 am

ray.wurlod wrote:What key are you using for the lookup? if its only 1234 - note that all of these have 1234 - then the first one found is returned. You need more specific information for this kind of lookup.

I am using Make and incorrectmodel as Key

kumar_s · Post by **kumar_s** » Thu Mar 02, 2006 7:14 am

Is it Make = AcutalMake And Mode = IncorrectModel?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 02, 2006 7:15 am

Check that you really do have MAZDA MX-5 MX5 as a row in the lookup table (not MAZDA MX-3 MX5). Check also that there are no trailing spaces causing the lookup to fail.

dprasanth · Post by **dprasanth** » Thu Mar 02, 2006 7:15 am

kumar_s wrote:Is it Make = AcutalMake And Mode = IncorrectModel?

Yes Kumar it is.I am using the above lookup

dprasanth · Post by **dprasanth** » Thu Mar 02, 2006 7:18 am

ray.wurlod wrote:Check that you really do have MAZDA MX-5 MX5 as a row in the lookup table (not MAZDA MX-3 MX5). Check also that there are no trailing spaces causing the lookup to fail.

I did check right now and there is a row in the lookup table for MX-5. Just to make sure that there are no trailing spaces, I deleted the row and inserted it again. But still I am getting the same problem.

dprasanth · Post by **dprasanth** » Thu Mar 02, 2006 9:20 am

dprasanth wrote:
ray.wurlod wrote:Check that you really do have MAZDA MX-5 MX5 as a row in the lookup table (not MAZDA MX-3 MX5). Check also that there are no trailing spaces causing the lookup to fail.
I did check right now and there is a row in the lookup table for MX-5. Just to make sure that there are no trailing spaces, I deleted the row and inserted it again. But still I am getting the same problem.

Hi All,
I have got this problem solved now. Thanks a lot for all your replies. The problem was as follows
I had two transformer stages . So the field Model was undergoing couple of changes.So when doing the lookup and derivation I used the changed model field. But I tried by changing the lookup to the original model field from the file and the derivation to the changed model field and it worked. Once a again thanks a lot for all your time