Decimal conversion

[reachmexyz]

Hello all,

I am getting a decimal number like 0000345.2343 and i am trying to convert the decimal to string and trim the zeros. To conver to string, i have used decimaltostring(0000345.2343,"fix_zero") and ran the job. I am getting "Conversion error calling conversion routine string_from_decimal data may have been lost" error and data is getting lost.
Please let me know how can i convert a decimal to String and later trim the leading zeros.

[anbu]

Code: Select all

Trim(field,'0','L')

[reachmexyz]

Code: Select all

Trim(field,'0','L')

This will not work on a decimal directly.
Assuming decimal is 00023.22 and its schema is (precision=7, length=2),
even after trim(00023.22), the output will be 0000023.22 . Decimal number just fills in the precision and scale and doesnt care trim function.

Thats why i wish to convert the decimla to strign and then use trim function on that string.

[chulett]

Which is what was being suggested - trim the resulting string as shown.

[ray.wurlod]

Code: Select all

Trim(field,'0','L')

This will not work on a decimal directly.

Have you tried it? Decimal to string is an implicit conversion, so it should work.

[marpadga18]

Code: Select all

Trim(field,'0','L')

This will not work on a decimal directly.

Have you tried it? Decimal to string is an implicit conversion, so it should work.

I tried this Trim and converting from decimal to string also then also it is not is not working. Is there any other solution for this

[jwiles]

Try this instead:

Code: Select all

Stage Variable svDecimal varchar()
derivation:   field

Output Link or another Stage Variable:
Left(svDecimal,1) : Trim(Right(svDecimal,8),'0','L')

It's that pesky sign byte...the 0's are not leading because of it.

The length 8 in the Right() function is based on the instrinsic conversion result: (decimal length)+2-1. You could use Len(field)-1, but since the string will always be the same length (coming from the same decimal) that just wastes CPU.

Regards,