DSXchange

Hi,
I have an input stream where the field is decimal(18). It tries to lookup into a hashed file where the key is also decimal(18). When I view data, that lookup has no period, whereas the input stream value does.
The lookup fails.

I didn't think the period at the end of the number would matter (isn't 45. = 45 ?? ), but it looks like it does.

I've tried doing math on the input stream value (adding 0, multiplying by 1), but it rounds the value, which obviously is not what I want.

Any ideas?

{corrected smileys error}

Edit your first post and check "Disable Smilies in this post".

Any chance this is the classic floating point problem where the display shows you 45 but you have in one system 45.00000000000000001 and in the other system 44.999999999999999999999999 or some similar hidden microscopic difference in values?

Convert the values to raw hex and compare those in a peek for the offending row.

I spent a lot of time in non database programming and the rule of thumb back when was that you try to avoid a compare of floating point for equality (not counting 0.0).

Hi,

Not sure how to convert to raw hex - is it possible in a server job?

In any case, if I search for 45. (in view data) it fails. If I search for 45 (in view data) it succeeds.

If the number of decimal places is always the same, I'd try using Trim() or Convert() to get rid of the ".".

If the number of decimal places is not always the same, I'd try multiplying the numbers by a sufficiently large power of ten before getting rid of the ".".

And I'd probably report this problem to IBM as a likely bug.

Hi,
I tried doing arithmetic on the decimal(18), but it rounded the number, thereby corrupting it.

Trimming the period away was the solution. I used:
TrimF( Trim(input_stream.Column,'.') )
and that seemed to work.

thanks for the suggestions!

To re-iterate Ray's point, that's OK if your keys are truly integers that just happened to be stored in decimal format. However, if not, then you may have created a problem. For example,

Before trim
Lookup Key: 45.1
Data Key: 451.

After trim
Lookup Key: 451
Data Key: 451

Be careful!