DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
sec105105
Participant



Joined: 20 Mar 2009
Posts: 40
Location: Ottawa
Points: 439

Post Posted: Tue Mar 29, 2016 11:53 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Server
OS: Unix
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



Joined: 20 Mar 2009
Posts: 40
Location: Ottawa
Points: 439

Post Posted: Tue Mar 29, 2016 12:03 pm Reply with quote    Back to top    

{corrected smileys error}


Last edited by sec105105 on Tue Mar 29, 2016 12:52 pm; edited 1 time in total
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42273
Location: Denver, CO
Points: 217068

Post Posted: Tue Mar 29, 2016 12:16 pm Reply with quote    Back to top    

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

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 273

Points: 2756

Post Posted: Tue Mar 29, 2016 12:27 pm Reply with quote    Back to top    

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).
Rate this response:  
Not yet rated
sec105105
Participant



Joined: 20 Mar 2009
Posts: 40
Location: Ottawa
Points: 439

Post Posted: Tue Mar 29, 2016 12:55 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54070
Location: Sydney, Australia
Points: 293276

Post Posted: Tue Mar 29, 2016 5:25 pm Reply with quote    Back to top    

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 number ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
sec105105
Participant



Joined: 20 Mar 2009
Posts: 40
Location: Ottawa
Points: 439

Post Posted: Wed Mar 30, 2016 7:08 am Reply with quote    Back to top    

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!
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1637
Location: Colleyville, Texas
Points: 22256

Post Posted: Wed Mar 30, 2016 9:11 am Reply with quote    Back to top    

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 - 2017
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours