Decimal to String
Moderators: chulett, rschirm, roy
Decimal to String
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?
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?
-Thanks
Ezapa
Ezapa
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Code: Select all
DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Col1 is decimal data type in the source.is field function is valid on this column?ssnegi wrote:This will remove only the leading zeros and keep the trailing zeros.Code: Select all
DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not true. Observe the solution, in which a decimal placeholder was explicitly concatenated.ssnegi wrote:Yes field function can be used to extract from a decimal keeping the decimal point as the delimiter.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I used a row generator to generate a decimal with specs 25 (length 10, Scale 5)
Then I applied the transform in the transformer.
It gave the result in a Varchar column: 25.00000
In another field defined as varchar I used the derivation :
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.
Then I applied the transform in the transformer.
Code: Select all
DecimalToString(Field(DSLink.COL,'.',1),"suppress_zero"):'.':Field(DSLink.COL,'.',2)
In another field defined as varchar I used the derivation :
Code: Select all
field(DSLINK.COL,'.',1)
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: