remove leading zeros in decimal field in sequential file
Moderators: chulett, rschirm, roy
remove leading zeros in decimal field in sequential file
Hi,
I am trying to remove the leading zeros in the decimal field in a sequential file stage.
for example...
if we have a value 000000000000000032.00
i want it to be changed as 32.00 .
please let me know if there is any way i can do it .
My input is a database and i am trying to put the data into a sequential file
THank you in advance
I am trying to remove the leading zeros in the decimal field in a sequential file stage.
for example...
if we have a value 000000000000000032.00
i want it to be changed as 32.00 .
please let me know if there is any way i can do it .
My input is a database and i am trying to put the data into a sequential file
THank you in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You will need to change the data type to Varchar. DataStage will always add leading and trailing nonsignificant zeroes to Decimal data type when writing to text file or displaying through data browser. This proves that precision and scale are being handled properly.
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.
Removing leading zeros
Even after converting into varchar...the leading zeros come up....is there any other way i cna get rid of the leading zeros
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
I hope this will solve your issue..
sAM
Code: Select all
Trim(DecimalTostring(In.Col),'0','L')
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
C_format
I observed that all posts related to this issue has the similar solution which says that we have to read the output as varchar and use DecimalToString in Transformer..
But when we were faces with this issue, one of my peers suggested using C_format option in the column properties...and it worked..Anyone here who disagrees?
But when we were faces with this issue, one of my peers suggested using C_format option in the column properties...and it worked..Anyone here who disagrees?
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Use DecimalToString() with "suppress_zero" option. Something like
EDIT: suppress should have two P's as mentioned by Ray.
Code: Select all
DecimalTostring(In.Col, "suppress_zero")
EDIT: suppress should have two P's as mentioned by Ray.
Last edited by priyadarshikunal on Mon Sep 20, 2010 4:59 am, edited 1 time in total.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Thanks Ray for correcting it.
you should only use it when the you don't have precision associated with the decimal value. Also integer datatype's storage (i think even bigint) may be insufficient to store decimal(38,0).
you should only use it when the you don't have precision associated with the decimal value. Also integer datatype's storage (i think even bigint) may be insufficient to store decimal(38,0).
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 12
- Joined: Wed Oct 03, 2007 9:11 am
- Location: London
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI