how to convert into decimal format

[skp]

I have source like below with Varchar(19)

-000000000001289000
+000000123456789000

I am using StrinToDecimal(Input) and giving data type as Decimal(18,4)

But in dataset it showing for first field is as
-00000000000128.9000

for second field it showing all zeros
00000000000000.0000
and also I am getting warning
APT_CombinedOperatorController,1: Conversion error calling conversion routine decimal_from_ustring data may have been lost

Please help me on this what I am doing wrong here

[SURA]

It seems few guys are fishing for the same question. Read the below link which i copied from the other thread. Try to understand. Come back with your understanding as well as what you have tried!

http://pic.dhe.ibm.com/infocenter/iisin ... tions.html

[skp]

I had already refered the document and also searched in dsxhange but not getting the correct answers.

I am applying the function like below
StringToDecimal(Inputcolumn/10000)

As I mentioned in my earlier post that for the value -000000000001289000

I am getting correct output that -128.9000
but for the value +001234567811289000 I am getting
0000000000000.0000

I am not understanding why first value is giving correct output na dwhy not for second value.

[ArndW]

What about if you do StringToDecimal(Convert('+','',001234567811289000 )/1000)

[skp]

still problem is there.

I tried with (StringToDecimal(Input)/10000). My output is coming as expected but still I am getting warning
"Conversion error calling conversion routine decimal_from_ustring data may have been lost"

Any idea why I am getting this warning how to remove it without message handler.

[ray.wurlod]

How about adding a couple of places to the precision? At least as an intermediate step, specify Decimal(20,4) rather than Decimal(18,4).

[miwinter]

I'm slightly intrigued as to why there is a reference to decimal_from_ustring in there for a varchar - are you using extended metadata options? I wouldn't be surprised also if, under the covers, your transformation is going from a varchar to another datatype in an intermediate variable, before being output to the selected decimal form. It might be this is causing some interference here. I've tried in vain to find a reporting variable that would output this in the log, but could not. The only place I have seen it visible is when exporting the job design as a dsx and then it was visible under the transform operator, similar to

Code: Select all

	// declare our intermediate variables for this section
	dfloat InterVar0_1;
	int64 InterVar0_2;

You could also find it in the .trx file under the project too.

In this instance we have some odd behaviour with our output too, which I'm currently working through with the vendor. The above comes from an AsInteger(StringToDecimal)) function combination.

[ray.wurlod]

Most of the type conversion functions in the Transformer stage are actually implemented as modify operators under the covers. Thus, for example, StringToDecimal() function in Transformer stage is actually implemented as the decimal_from_string() or decimal_from_ustring() function in Orchestrate depending on whether or not NLS is enabled.