Page 1 of 1
Decimal Conversion error
Posted: Fri Apr 30, 2010 12:57 am
by kamalshil
Hi,
In my parallel job
i am trying to convert the string value into decimal.
I have below as input.
Code: Select all
1. 178.870230769231
2. 0.01
3. 327.67
4. 177.2412
So for this conversion i am writing the code as:
Code: Select all
If IsNull(out.AMOUNT) Then 0 Else StringToDecimal((TrimLeadingTrailing(out.AMOUNT)))
But i am getting :
APT_CombinedOperatorController,0: Conversion error calling conversion routine decimal_from_string data may have been lost
Please help me were i am going wrong in this.
input column is Varchar(17)
output column metadata : Amount(38,10).
Posted: Fri Apr 30, 2010 2:07 am
by ray.wurlod
Your first value has 12 digits following the decimal placeholder, but your Decimal data type's scale only allows for 10. DataStage is alerting you to the fact that the least significant digits have probably been lost.
Posted: Fri Apr 30, 2010 2:17 am
by kamalshil
ray.wurlod wrote:Your first value has 12 digits following the decimal placeholder, but your Decimal data type's scale only allows for 10. DataStage is alerting you to the fact that the least significant digits have p ...
Ok so the problem is with my scale setting.
But when i set scale to 14 also i got the same error.
Code: Select all
test: 000000000000000000000000.00000000000000
what scale shall i set.so it can accommodate all the above scenarios.
And i am getting this error for all the input rows
Posted: Fri Apr 30, 2010 2:19 am
by ray.wurlod
Hard to tell without knowing your data. Keep edging it up till the problem goes away. You may need to increase precision also, if scale gets too close to it.
Posted: Fri Apr 30, 2010 2:31 am
by kamalshil
ray.wurlod wrote:Hard to tell without knowing your data. Keep edging it up till the problem goes away. You may need to increase precision also, if scale gets too close to it. ...
Sample Data:
Code: Select all
0.01
177.2412
327.67
0.01
195.04078125
208.04
191.353076923077
197.856666666667
203.63128113879
198.6475
282.325
0.0089247311827957
177.24
208.04
197.855
203.631388888889
203.63
203.6325
177.24
327.674
195.04
203.6325
203.632
Posted: Fri Apr 30, 2010 2:43 am
by dspxlearn
Since, the source is a varchar field, wondering if you are geting any spaces or empty from it!!
If so, you might get this error. Check this way -
If Trim(NullToValue(out.AMOUNT,'0')) = '0' then 0
Else
StringToDecimal((TrimLeadingTrailing(out.AMOUNT)))
Please let us know the results.
Posted: Fri Apr 30, 2010 2:44 am
by kamalshil
dspxlearn wrote:Since, the source is a varchar field, wondering if you are geting any spaces or empty from it!!
If so, you might get this error. Check this way -
If Trim(NullToValue(out.AMOUNT,'0')) = '0' then 0
Else
StringToDecimal((TrimLeadingTrailing(out.AMOUNT)))
Please let us know the results.
Already i did try this then also getting the same error.
Posted: Fri Apr 30, 2010 7:52 am
by rohithmuthyala
By chance is there any possibilty of getting a non numeric character in the input field like an alphabet or a box character ?
Posted: Sun May 02, 2010 11:43 pm
by dspxlearn
Check the value from the source and then we shall go from there...