Decimal To String Conversion

jerome_rajan · Post by **jerome_rajan** » Mon Jun 10, 2013 1:07 am

Hi All,

I am trying to convert a source column with data type -DECIMAL(10) to VARCHAR(10). I realise that this would result in conversion errors since the target length would have to account for the sign and the decimal point in the source.
I'm currently implementing this by converting the datatype in my source SQL using CAST. Can someone please throw light on how I can achieve this conversion in the transformer?

Thanks

chulett · Post by **chulett** » Mon Jun 10, 2013 6:16 am

I assume you've tried using the DecimalToString() function, can you detail for us the issues you've had with that? I'd also be curious what your CAST looks like to see how you are handling it there.

jerome_rajan · Post by **jerome_rajan** » Mon Jun 10, 2013 6:27 am

Yes Craig. I tried using the DecimalToString() function but it throws a conversion error and outputs a blank value. Ditto if I increase the target length by 1. But if I make the target length varchar(12), the data loads fine. I assume this is because a decimal outputs 2 additional characters - one for the decimal point and one for the sign. Please correct my understanding here.

As for the conversion in the SQL, I used a

Code: Select all

SELECT CAST(colname AS VARCHAR(10)) FROM TABNAME

chulett · Post by **chulett** » Mon Jun 10, 2013 6:34 am

Can you post the actual conversion error? Sorry, just trying to get all of the forensic information here.

jerome_rajan · Post by **jerome_rajan** » Mon Jun 10, 2013 6:40 am

Sure

Code: Select all

Conversion error calling conversion routine string_from_decimal data may have been lost.

rsomiset · Post by **rsomiset** » Mon Jun 10, 2013 11:40 am

Is '1' the scale for your decimal ? Also, what is the maximum value you are passing when you are getting the error ?? You need to account for minus sign too while calculating the length(i.e. length of decimal + 1) should be your target column length for varchar field to avoid this message.

--
Rajesh

jerome_rajan · Post by **jerome_rajan** » Mon Jun 10, 2013 11:47 am

I know I have to account for the Sign and the Decimal as I've already mention in my post. The actual number of digits don't seem to matter. Whether I pass 500 or 1234567890 , the result remains the same. Decimal(10,0) will just not enter varchar(10) without
1. Using a cast and converting it at the source if the source is a database.
2. Increasing the target length by (Precision Of Decimal) + 2

I just want to know if there's a way to handle this scenario in a transformer.

FranklinE · Post by **FranklinE** » Mon Jun 10, 2013 12:11 pm

Jerome,

I have a standard and successful usage of DecimalToString based on the length+2 principle: choose a standard length for the VarChar that will always be long enough no matter the size of the input column.

I deal exclusively with dollar amounts, so I chose VarChar(22) because that is the length of the destination column for dollar amounts. After accounting for the sign, decimal and cents, it leaves up to 18 bytes for the integer portion of the decimal. No conversion errors at all in these jobs.

jerome_rajan · Post by **jerome_rajan** » Mon Jun 10, 2013 12:21 pm

I get that Franklin as I've already mentioned. My question here is whether this is something that CAN be handled in a transformer and if yes, how?

FranklinE · Post by **FranklinE** » Mon Jun 10, 2013 4:16 pm

Sorry, Jerome, I misunderstood your question. My code is in the Transformer stage, and I apply it both in the main derivations and in stage variables.

jerome_rajan · Post by **jerome_rajan** » Tue Jun 11, 2013 1:27 am

Achieved it with a combination of stage variables and substring functions. Thanks