Decimal Conversion error

[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).

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

[kamalshil]

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

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

[kamalshil]

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

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

[kamalshil]

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.

[rohithmuthyala]

By chance is there any possibilty of getting a non numeric character in the input field like an alphabet or a box character ?

[dspxlearn]

Check the value from the source and then we shall go from there...