remove leading zeros in decimal field in sequential file

UPS · Post by **UPS** » Mon Mar 16, 2009 4:31 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Mar 16, 2009 5:08 pm

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.

UPS · Post by **UPS** » Tue Mar 17, 2009 8:54 am

Even after converting into varchar...the leading zeros come up....is there any other way i cna get rid of the leading zeros

chulett · Post by **chulett** » Tue Mar 17, 2009 9:08 am

How exactly are you "converting into varchar"?

us1aslam1us · Post by **us1aslam1us** » Tue Mar 17, 2009 1:43 pm

I hope this will solve your issue..

Code: Select all

Trim(DecimalTostring(In.Col),'0','L')

sAM

abhilashnair · Post by **abhilashnair** » Mon Jul 20, 2009 6:54 am

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?

priyadarshikunal · Post by **priyadarshikunal** » Mon Jul 20, 2009 7:41 am

Use DecimalToString() with "suppress_zero" option. Something like

Code: Select all

DecimalTostring(In.Col, "suppress_zero")

EDIT: suppress should have two P's as mentioned by Ray.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 20, 2009 4:45 pm

Double "P" in "suppress".

algfr · Post by **algfr** » Tue Jul 21, 2009 4:44 am

Correct me if I'm wrong but cannot you use Integer type instead of decimal ?

priyadarshikunal · Post by **priyadarshikunal** » Tue Jul 21, 2009 5:10 am

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).

algfr · Post by **algfr** » Tue Jul 21, 2009 5:30 am

True, but maybe he could tell us how much he needs because 38 digits, man, that's a lot.

ragasambath · Post by **ragasambath** » Tue Jul 21, 2009 6:11 am

Hello,

The function is DecimalTostring(Decimal input, "supress_zero")

Before using this function covert your input to Decimal

Thanks

Ragasambath

ShaneMuir · Post by **ShaneMuir** » Tue Jul 21, 2009 6:16 am

If you use the "supress zero" option on the decimaltostring, it will remove zero's after the decimal also. So if the value was 32.000 it would come out as 32

PhilHibbs · Post by **PhilHibbs** » Mon Sep 20, 2010 2:52 am

Where is this documented?

priyadarshikunal · Post by **priyadarshikunal** » Mon Sep 20, 2010 4:57 am

The function is documented in "Parallel Job Developer Guide" however the different rtypes are not really present there. The different options are documented in "Orchestrate Operator Reference" doc.

DSXchange

remove leading zeros in decimal field in sequential file

remove leading zeros in decimal field in sequential file

Removing leading zeros

C_format