DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
Mohan09
Participant



Joined: 18 Jul 2018
Posts: 4

Points: 102

Post Posted: Mon Nov 05, 2018 6:33 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Have a Parallel Job to load the source data into a staging table. Source file has all fields comes in Varchar Format and during loading , i'm converting string to decimal for all decimal fields. If we have a numerical value then loading job completed successfully with no warnings. But if any one of the decimal field has blanks or nulls than i'm getting warning message "Conversion error calling conversion routine decimal_from_string data may have been lost".

For (e.g), my source file has the below columns
ID,INT_RATE,INT_AMT,INT_PEND

Source Datatype
ID - VARCHAR(3),INT_RATE-VARCHAR(7),INT_AMT-VARCHAR(7),INT_PEND-VARCHAR(7)

Values are:
100|10.1|12.2|25.5
101|22.2|55.67|33.78
102||||

Target Datatype
ID - VARCHAR(3),INT_RATE-DECIMAL(5,2),INT_AMT-DECIMAL(5,2),INT_PEND-DECIMAL(5,2)

In order to avoid the warning, have coded like below:
if (INT_RATE = ' ' OR IsNUll(INT_RATE) =1) then Stringtodecimal(0) Else
Stringtodecimal(INT_RATE).

Even after added this condition, i'm getting the warnings. Can someone help me to get rid of this warnings. Thanks..

_________________
Regards,
Mohan
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42792
Location: Denver, CO
Points: 220559

Post Posted: Tue Nov 06, 2018 6:12 am Reply with quote    Back to top    

Well... perhaps it's as simple as this:

Code:
if (INT_RATE = ' ' OR IsNUll(INT_RATE) =1) then Stringtodecimal("0") Else
Stringtodecimal(INT_RATE)

_________________
-craig

"I don't mind you comin' here and wastin' all my time time"
Rate this response:  
Not yet rated
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 706
Location: Malvern, PA
Points: 6714

Post Posted: Tue Nov 06, 2018 12:28 pm Reply with quote    Back to top    

Your input columns are all VarChar(7), your output column is Decimal(5,2). You have two fewer bytes on output, and unless you either trim the string to 5 bytes or increase the decimal to 7,2 you will continue to have the warning.

_________________
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
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: 42792
Location: Denver, CO
Points: 220559

Post Posted: Tue Nov 06, 2018 4:25 pm Reply with quote    Back to top    

You make a valid point but they stated it was only happening when if "any one of the decimal field has blanks or nulls" so guessing that assertion may not, in fact, be true.

_________________
-craig

"I don't mind you comin' here and wastin' all my time time"
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