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.
Lookup not returning the right row
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.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.
Hi All,dprasanth wrote: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.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 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