Conversion from string to decimal

Nagac · Post by **Nagac** » Mon Apr 04, 2011 5:17 am

Hi

I have data coming in character format like 98789.99. After reading this, i need to sum up all these values in Aggregator stage. For that i tried modify stage to convert string to decimal. i got the error as below

Modify_319: When checking operator: When binding output schema variable "outRec": When binding output interface field "Filed_Name" to field "Filed_Name": Conversion from source type "string[9]" to result type "decimal[1,0]": Possible range limitation.

I used like this :

INSURANCE_LIABILITY_AMOUNT:decimal = decimal_from_string (INSURANCE_LIABILITY_AMOUNT)

abhilashnair · Post by **abhilashnair** » Mon Apr 04, 2011 5:27 am

You need to mention the Length and scale in modify stage output metadata as otherwise it will take the default value. The error is because the incoming data is of length 9 and you have not mentioned any length or scale in the modify stage metadata

Nagac · Post by **Nagac** » Mon Apr 04, 2011 5:31 am

Thanks mate,

I have given the length and scale in output metadata in modify stage

abhilashnair · Post by **abhilashnair** » Mon Apr 04, 2011 5:43 am

Nagac wrote:Thanks mate,

I have given the length and scale in output metadata in modify stage

And....? Is the issue resolved ?

Nagac · Post by **Nagac** » Mon Apr 04, 2011 5:51 am

Sorry, i didn't reply properly.

Its not resolved. i got the above error after i mention precisio, and scale in output metdata in modify stage.

Nagac · Post by **Nagac** » Mon Apr 04, 2011 5:53 am

I used modify stage before aggregating the data.

Nagac · Post by **Nagac** » Mon Apr 04, 2011 8:52 am

I have changed it in Transformer and not its fine.

But i am facing one more issue that After Aggregation i used modify stage to convert double to decimal using default like Column_Name=Column_Name. It is giving the proper result, but its not adding zeros after decimal point if the data is rounded eg: 100.00 it is showing only 100, 100.10==>100.1.

I need to display in 100.00 format.

Is it possible?

ray.wurlod · Post by **ray.wurlod** » Mon Apr 04, 2011 4:19 pm

Yes, but remember that leading and trailing non-significant zeroes are not actually part of a Decimal number. Therefore, the display specifications are applicable only when the target data type is string. You could try applying a DecimalToDecimal() function with scale of 2 in the metadata.

Nagac · Post by **Nagac** » Tue Apr 05, 2011 2:32 am

Thanks Ray,

I did in modify stage as below

I am using below syntax in modify stage to keep 2 decimal places as scale. Even it is 00 it should as 00.00
I used syntax as:
Total_Record_Count = DecimalToDecimal(Total_Record_Count,"round_inf")

I am getting error like:
Error parsing modify adapter: Error in binding: Unknown conversion: DecimalToDecimal
Expected destination field selector, got: ")"; input:
Total_Record_Count = DecimalToDecimal(Total_Record_Count,"round_inf")

Input is decimal format and output also decimal format

Nagac · Post by **Nagac** » Tue Apr 05, 2011 4:47 am

I have done this using decimal_from_decimal [round_inf]() in modify stage. But the result is same as earlier.

Can anyone tell how to do this?

chulett · Post by **chulett** » Tue Apr 05, 2011 7:05 am

As Ray noted, display formats are only valid when the target data type is string.

Nagac · Post by **Nagac** » Mon Apr 11, 2011 7:25 am

i have given decimaltodecimal(field) and give metada as 10,2

But it is giving sama like 100

i need 100.00

i dont need to it to be in decimal format can be any, so anybody give me suggestions

chulett · Post by **chulett** » Mon Apr 11, 2011 7:32 am

As already noted, a string target combined with the DecimalToString() function.

Nagac · Post by **Nagac** » Mon Apr 11, 2011 7:37 am

After using thse DecimalToString() function it is giving 00100.00.

I used Trim(Field,'0','L') to remove the left side zero's. it's not removing zero's. it is giving same as 00100.00

Nagac · Post by **Nagac** » Mon Apr 11, 2011 7:40 am

I used Same function in other transformation, it worked fine. But dont know why it is not working here.

FYI my stages: Input-->Aggregator(Here i am summing the data)-->Transformer