Lookup fails if key is decimal, and input has a period

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sec105105
Participant
Posts: 44
Joined: Fri Mar 20, 2009 7:21 am
Location: Ottawa

Lookup fails if key is decimal, and input has a period

Post by sec105105 »

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?
Last edited by sec105105 on Tue Mar 29, 2016 12:50 pm, edited 1 time in total.
sec105105
Participant
Posts: 44
Joined: Fri Mar 20, 2009 7:21 am
Location: Ottawa

typos...

Post by sec105105 »

{corrected smileys error}
Last edited by sec105105 on Tue Mar 29, 2016 12:52 pm, edited 1 time in total.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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).
sec105105
Participant
Posts: 44
Joined: Fri Mar 20, 2009 7:21 am
Location: Ottawa

conversion

Post by sec105105 »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sec105105
Participant
Posts: 44
Joined: Fri Mar 20, 2009 7:21 am
Location: Ottawa

Post by sec105105 »

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!
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

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!
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
Post Reply