Decimal issue

somu_june · Post by **somu_june** » Sun Jan 30, 2011 7:49 pm

Hi All,

I have Amount with Varchar(21) and it's getting data as 11302.06 and 0.00 in two records and I'm converting it to Decimal (21,5). I'm using a function

StringToDecimal(SvValueAddedAmt,"trunc_zero")

but I'm getting data as 0000000000011302.06000 and 0000000000000000.00000

Is there a way that I can get output as 11302.06000 and 0.00000

Thanks,
Somaraju

ray.wurlod · Post by **ray.wurlod** » Sun Jan 30, 2011 8:11 pm

Not if your data type is Decimal. DataStage shows leading and trailing zeroes to confirm that precision and scale have been handled correctly.

You can remove these by changing the data type to VarChar.

chulett · Post by **chulett** » Sun Jan 30, 2011 10:25 pm

Why not just append the needed number of zeroes to the string?

Shruthi · Post by **Shruthi** » Mon Jan 31, 2011 2:24 am

You can give suppress_zero after conversion. This would result in 11302.06.

If you want to ginore only zero's in mantissa, then convert it to varchar again and trim zero's on the left.

Hope this helps!

richdhan · Post by **richdhan** » Mon Jan 31, 2011 5:43 am

Hi,

Pls search for my posts on DecimalToString. Along with the DecimalToString you have to use the Field and Trim options to remove the unwanted zeros.

HTH
--Rich

iHijazi · Post by **iHijazi** » Wed Feb 02, 2011 12:05 am

Of course you'd have it look like that!

Here's what to do: in the stage before that stage which got the varchar type, it's either numeric or decimal, correct?

Now, in that stage, clear the precision or limit it.

If the stage before it also have the type of varchar, keep tracking back the stages till you hit one that is NOT varchar and is decimal/numeic.

Happy DataStaging