How is a lookup performed on fields that are declared to be numeric both in stream and reference input? I would have expected that it would be done numerically, but it seems that a record can't be matched, if the lookup has 1.0 instead of 1.00?
Stephan
lookup on numeric fields
Moderators: chulett, rschirm, roy
Stephan
Change your metadata to varchar and see what it is doing. Maybe put it in debug mode.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Change your metadata to varchar and see what it is doing. Maybe put it in debug mode.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Kim
I tried something similar formatting the numbers to have two decimal places, which dramatically increased the hit ratio. Till now, I only had integers within key columns, so I never became aware that the comparison doesn't seem to be numerical, but character by character, thus '.5' not matching '0.5', or similar. At least, if the lookup is done against a hash file or uv-table, database lookups might work different.
Stephan
I tried something similar formatting the numbers to have two decimal places, which dramatically increased the hit ratio. Till now, I only had integers within key columns, so I never became aware that the comparison doesn't seem to be numerical, but character by character, thus '.5' not matching '0.5', or similar. At least, if the lookup is done against a hash file or uv-table, database lookups might work different.
Stephan
Stephan
Changing it to a UV stage should not make a difference. The metadata is what is causing all the problems. When you create the hash make the key field varchar as well. Use it as varchar all the way across all jobs. It will work this way. You have to be consistant.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Changing it to a UV stage should not make a difference. The metadata is what is causing all the problems. When you create the hash make the key field varchar as well. Use it as varchar all the way across all jobs. It will work this way. You have to be consistant.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Stephan
Unless this is a calculated field then this should work but if you need to format to a specific number of decimals then oconv() or fmt() will do the trick. To format to 3 decimals:
oconv(iconv(myNumber, "MD3"), "MD3")
fmt(myNumber, "9R3")
You may want to round the iconv() part because this truncates. This will work on a varchar field. If you use metadata other than varchar then DataStage does some kind of internal conversion. There are probably other metadata types not converted by DataStage. I am sure Ray or Ken know but for debuging purposes the safest thing is to use varchar until you get it working.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Unless this is a calculated field then this should work but if you need to format to a specific number of decimals then oconv() or fmt() will do the trick. To format to 3 decimals:
oconv(iconv(myNumber, "MD3"), "MD3")
fmt(myNumber, "9R3")
You may want to round the iconv() part because this truncates. This will work on a varchar field. If you use metadata other than varchar then DataStage does some kind of internal conversion. There are probably other metadata types not converted by DataStage. I am sure Ray or Ken know but for debuging purposes the safest thing is to use varchar until you get it working.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
IMPORTANT SAFETY TIP:
DataStage Server Edition by default uses STRING comparisons. So, 10.0 is not the same as 10. The only way to get EQUALITY expressions to work is to use explicit formatting. So, if you have two values to compare, make sure you have formatted them. If you want to compare dates, make sure you have used a standard format ACROSS YOUR ENTIRE ETL APPLICATION (my recommendation being YYYY-MM-DD HH:MI:SS because it ALWAYS WORKS for > or < expresssions).
Kenneth Bland
DataStage Server Edition by default uses STRING comparisons. So, 10.0 is not the same as 10. The only way to get EQUALITY expressions to work is to use explicit formatting. So, if you have two values to compare, make sure you have formatted them. If you want to compare dates, make sure you have used a standard format ACROSS YOUR ENTIRE ETL APPLICATION (my recommendation being YYYY-MM-DD HH:MI:SS because it ALWAYS WORKS for > or < expresssions).
Kenneth Bland