Decimal to String

[Ezapa]

Hello,

The source is SQL server Database.
Col1 - Decimal(18,2),Null ;Col2 - Decimal(18,0),Null .
The target is a Sequential file.

Job Design:

ODBC stage--->Transformer--->Sequential file

The requirement is :

Source data

Col1 - 400.00 ; Col2 - 20

Target expected data

Col1 - 400.00 ; Col2 - 20

But when I am trying to extract the data for Col1 ,Col2 with Datatype as Decimal ,the leading zero's are being populated i.e. 0000000000000400.00 .Then I used Decimal to string(col1,''suppress_zero'') in Transformer stage, the result is suppressing the zero's after the decimal point too i.e. 400 . But I need to get 400.00

Am I missing something?

[qt_ky]

Double check the data type of your target column to make sure it is a string, like varchar, then it should suppress the zeros. If it is still set as a decimal type then it would explain the zeros you're still seeing.

[ssnegi]

Code: Select all

DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2)

This will remove only the leading zeros and keep the trailing zeros.

[Ezapa]

Thank you for the response.I used the Field function and its working as expected.

[qt_ky]

Just to confirm, the suppress option works as expected on version 8.7 (tested). Field function is not necessary. Am wondering why it doesn't work as expected for others.

[ssnegi]

The suppress option removes all the leading and trailing zeros. The user wants only the leading zeros to be removed...not the trailing zeros.
If the value is 00400.00 the suppress option will make it 400. The user wants 400.00

[ray.wurlod]

To clarify, the "suppress_zero" option removes only non-significant zeroes; that is, those to the left of that part of the decimal number to the left of the decimal placeholder and those following any non-zero digits to the right of the decimal placeholder.

[qt_ky]

Ah, I missed the whole point about the zeros after the decimal; had only noticed the complaint about leading zeros.

[vamsi.4a6]

Code: Select all

DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2)

This will remove only the leading zeros and keep the trailing zeros.

Col1 is decimal data type in the source.is field function is valid on this column?

[ssnegi]

Yes field function can be used to extract from a decimal keeping the decimal point as the delimiter.

[chulett]

Yes field function can be used to extract from a decimal keeping the decimal point as the delimiter.

Interesting... and all these years I thought the Field() function only worked on delimited strings.

[ray.wurlod]

Yes field function can be used to extract from a decimal keeping the decimal point as the delimiter.

Not true. Observe the solution, in which a decimal placeholder was explicitly concatenated.

[ssnegi]

I used a row generator to generate a decimal with specs 25 (length 10, Scale 5)
Then I applied the transform in the transformer.

Code: Select all

DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2) 

It gave the result in a Varchar column: 25.00000
In another field defined as varchar I used the derivation :

Code: Select all

field(DSLINK.COL,'.',1)

It gave the result : 00025
In another column I allowed the original value to flow through.
It gave result in a decimal column: 00025.00000
So the field operator does use the . as delimiter for extracting from a decimal.

[ray.wurlod]

But not "keeping the decimal point". The Field() function never keeps the leading or trailing delimiter.

[ssnegi]

The statement in its entirety is below :
"keeping the decimal point as the delimiter."
This translates to using the decimal as the delimiter in the field function.
Since the field function does not retain the delimiter I have concatenated the decimal point into the string.