String To Decimal Conversion Error

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Mohan09
Participant
Posts: 4
Joined: Wed Jul 18, 2018 8:57 pm

String To Decimal Conversion Error

Post by Mohan09 »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

Code: Select all

if (INT_RATE = ' ' OR IsNUll(INT_RATE) =1) then Stringtodecimal("0") Else 
Stringtodecimal(INT_RATE)
-craig

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

This from memory (away from DataStage at the moment). Isn't the default that decimal data type is not allowed to have zero value, but there is a flag in the conversion routines that permits zero-valued zeroes? I think the flag argument is fix_zero.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply