Page 1 of 1
Removing zeros for decimal values
Posted: Mon Sep 15, 2008 2:47 am
by dodda
Hi all,
I need some help regarding removing zeros for decimal values.
My source is oracle database. iam having one column QTY of decimal datatype(38,10)
And my target is .txt file and QTY column is of string type.
My input data is ::::
0000000000000000000000000010.1000000000
0000000000000000000000000020.0200000000
I have to produce in this format::
10.1
20.02
Plz help in getting the outptut.
Your help is greatly appreciated.
Re: Removing zeros for decimal values
Posted: Mon Sep 15, 2008 3:53 am
by dsusr
Try DecimalToString function with "suppress_zero" parameter
Re: Removing zeros for decimal values
Posted: Mon Sep 15, 2008 10:24 am
by ppavani_km
Try this function
Trim(DecimalToString(Coloumn_name,"suppress_zero"), "0", "L")
dodda wrote:Hi all,
I need some help regarding removing zeros for decimal values.
My source is oracle database. iam having one column QTY of decimal datatype(38,10)
And my target is .txt file and QTY column is of string type.
My input data is ::::
0000000000000000000000000010.1000000000
0000000000000000000000000020.0200000000
I have to produce in this format::
10.1
20.02
Plz help in getting the outptut.
Your help is greatly appreciated.
Hi
Posted: Mon Sep 15, 2008 12:12 pm
by neena
Try this DecimalToString(Column_name,""fix_zero,suppress_zero") and change the output Column data type to Varchar.
Posted: Mon Sep 15, 2008 3:56 pm
by ray.wurlod
If the data type is Decimal you can not remove the non-significant zeroes, because they don't actually exist. They are an artifact of how DataStage displays decimal numbers to prove that precision and scale are being handled correctly. Internally Decimal data type is stored in a binary format.
Posted: Tue Sep 16, 2008 9:10 am
by dsusr
ray.wurlod wrote:If the data type is Decimal you can not remove the non-significant zeroes, because they don't actually exist. They are an artifact of how DataStage displays decimal numbers to prove that precision an ...
Ray,
Since the person has the target column as String so I suppose he can easily remove the data from Source Decimal column by using the DecimalToString funtion.
Let me know if I am wrong.....
Re: Removing zeros for decimal values
Posted: Tue Sep 16, 2008 3:42 pm
by AKUMAR21
The simplest way to deal with it is to change the dataype of the column QTY to float with scale 2. It will do the required conversion without need of using any functions.
Posted: Tue Sep 16, 2008 5:02 pm
by ray.wurlod
If the data type of the output is string, then DecimalToString() may or may not be needed. It may be able to occur as an implicit conversion.
In that case, you could get rid of leading and trailing zero characters with a Trim() function.