Decimal Conversion Error

chaks · Post by **chaks** » Sat Sep 20, 2008 6:29 am

Hello DS Experts,
I am trying to convert a string to Decimal and getting the following errors.
1. Transformer_11,1: Numeric string expected of the appropriate decimal precision . Use default value.
2. Transformer_11,0: Conversion error calling conversion routine decimal_from_string data may have been lost.

Additional Info:- I am getting the data with scale 5 and negative numbers like (0.12345). I am using a stage variable

Code: Select all

svAmount = Trim(Convert('(,)','-,',(Convert(',','',Trim(DSLink2.CS_TRN_GC)))) )

This will convert () to Minus and strip commas,This is working fine. In the derivation I am using

Code: Select all

 StringToDecimal(SvAmount) * 1000 - Target column is 17,2 need to round off or truncate to 2 decimal places

Please guide me to resolve this

ray.wurlod · Post by **ray.wurlod** » Sat Sep 20, 2008 6:49 am

How do you know that the stage variable derivation is working? You strip the "," characters in the inner Convert() expression then attempt to convert "," to "," in the outer Convert() expression.

What problem (if any) are you having with the derivation expression? What is it producing? What do you expect that it should produce?

(0.12345) gets converted to -0.12345
You change the data type of that to Decimal and it should still be -0.12345.
You multiply that by 1000, and now have -123.45 which has the correct number of decimal places. I think it should work.

You might like to try StringToDecimal(svAmount * 1000) - with an rtype of round_inf if you wish.

chaks · Post by **chaks** » Sat Sep 20, 2008 7:31 am

ray.wurlod wrote:How do you know that the stage variable derivation is working? You strip the "," characters in the inner Convert() expression then attempt to convert "," to "," in the outer Convert() expression.

What problem (if any) are you having with the derivation expression? What is it producing? What do you expect that it should produce?

(0.12345) gets converted to -0.12345
You change the data type of that to Decimal and it should still be -0.12345.
You multiply that by 1000, and now have -123.45 which has the correct number of decimal places. I think it should work.

You might like to try StringToDecimal(svAmount * 1000) - with an rtype of round_inf if you wish.

Comma in first convert is a delimiter between (,),and I am sure because I am getting output like this.

Code: Select all

"OutPut_Value","Stage_Var_Value","Orig_Data"
" 00000000000000000.00000","7232.47136","7,232.47136
" 00000000000000000.00000","-97652.45044","(97,652.45044)

I tried StringToDecimal(svAmount * 1000,'Round_inf') but got the same output. Hold on,I just realized that while copying the output I got line breaks at the end of Stage_Variable output like this "7232.47136LineBreak",Is this the culprit,How can I deal with this.

chaks · Post by **chaks** » Sat Sep 20, 2008 11:12 am

Hold on,I just realized that while copying the output I got line breaks at the end of Stage_Variable output like this "7232.47136LineBreak",Is this the culprit,How can I deal with this.

There was some issue with usage of Convert ,I fixed it but still I am getting the Conversion Error

Code: Select all

Transformer_11,1: Conversion error calling conversion routine decimal_from_string data may have been lost

ray.wurlod · Post by **ray.wurlod** » Sat Sep 20, 2008 3:43 pm

What is the data type of the input column? If this can potentially contain a a string that is too large for Decimal(17,2) - in this case more than 18 characters long - then DataStage will always generate this alert warning.

chaks · Post by **chaks** » Sat Sep 20, 2008 5:56 pm

Hello Ray

Input column is Varchar (15) from a Sequential file

ray.wurlod · Post by **ray.wurlod** » Sun Sep 21, 2008 1:03 am

Looks like a case of "they're generating the alert no matter what", I guess in case the user supplies a string that can't be converted to decimal. Safely ignorable in my opinion; I'd emplace a message handler to demote its severity because of that.

chaks · Post by **chaks** » Mon Sep 22, 2008 7:40 am

Just got confirmation that it is a windows file, changed the Record Delimiter to DOS Format, and the problem is gone.

ds2000 · Post by **ds2000** » Wed May 16, 2012 4:02 pm

Had exactly same issue with a csv file where last column of the file was numeric and all conversion functions where throwing warnings. So converted line delimeter to UNIX type that fixed the problem.

ds2000 · Post by **ds2000** » Wed May 16, 2012 4:03 pm

Had exactly same issue with a csv file where last column of the file was numeric and all conversion functions where throwing warnings. So converted line delimeter to UNIX type that fixed the problem.