Page 1 of 2

Decimal to String

Posted: Mon Apr 28, 2014 1:26 pm
by 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?

Posted: Mon Apr 28, 2014 6:32 pm
by 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.

Posted: Mon Apr 28, 2014 7:02 pm
by 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.

Posted: Thu May 01, 2014 1:38 pm
by Ezapa
Thank you for the response.I used the Field function and its working as expected.

Posted: Thu May 01, 2014 1:49 pm
by 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.

Posted: Thu May 01, 2014 4:32 pm
by 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

Posted: Thu May 01, 2014 6:43 pm
by 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.

Posted: Thu May 01, 2014 7:47 pm
by qt_ky
Ah, I missed the whole point about the zeros after the decimal; had only noticed the complaint about leading zeros.

Posted: Sat May 03, 2014 3:43 am
by vamsi.4a6
ssnegi wrote:

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?

Posted: Sat May 03, 2014 4:04 am
by ssnegi
Yes field function can be used to extract from a decimal keeping the decimal point as the delimiter.

Posted: Sat May 03, 2014 2:17 pm
by chulett
ssnegi wrote: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.

Posted: Sat May 03, 2014 3:37 pm
by ray.wurlod
ssnegi wrote: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.

Posted: Sat May 03, 2014 4:55 pm
by 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.

Posted: Sat May 03, 2014 9:19 pm
by ray.wurlod
But not "keeping the decimal point". The Field() function never keeps the leading or trailing delimiter.

Posted: Sat May 03, 2014 9:26 pm
by 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.