Page 1 of 1
Decimal Conversion Error
Posted: Sat Sep 20, 2008 6:29 am
by chaks
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
Posted: Sat Sep 20, 2008 6:49 am
by ray.wurlod
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.
Posted: Sat Sep 20, 2008 7:31 am
by chaks
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.
Posted: Sat Sep 20, 2008 11:12 am
by chaks
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
Posted: Sat Sep 20, 2008 3:43 pm
by ray.wurlod
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.
Posted: Sat Sep 20, 2008 5:56 pm
by chaks
Hello Ray
Input column is Varchar (15) from a Sequential file
Posted: Sun Sep 21, 2008 1:03 am
by ray.wurlod
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.
Posted: Mon Sep 22, 2008 7:40 am
by chaks
Just got confirmation that it is a windows file, changed the Record Delimiter to DOS Format, and the problem is gone.
Posted: Wed May 16, 2012 4:02 pm
by ds2000
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.
Posted: Wed May 16, 2012 4:03 pm
by ds2000
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.