All Forums Database
ssi112 5 posts Joined 08/11
05 Feb 2015
Rounding: Result of division being forced to .000

I've read a few posts on this forum on how to force rounding to decimal places, but nothing is working. This is my latest attempt to try and have the results of a formula display decimals:
 ( CAST((CUPDAct / CityDelHrsAct) AS DECIMAL(6,2)) / CAST((CUPDSPLY / CityDelHrsSPLY)  AS DECIMAL(6,2)) - CAST(1.00 AS DECIMAL(6,2))) * CAST(100.00 AS DECIMAL(6,2)) AS ActPctSPLY
The result is 3.0000
That makes no sense to me. The result I want is 2.73 using this data:

(74.06 / 72.09 - 1) * 100 = 2.73


What is the solution to make Teradata round to decimals?


Thanks for the help


hemanth.gudela 10 posts Joined 01/15
05 Feb 2015

You can acheive this by increasing the decimal digits, and casting either numerator or denominator to decimal, instead of casting the fraction.

select ((CUPDAct/CAST(CityDelHrsAct AS DECIMAL(8,4)))/(CUPDSPLY / CAST(CityDelHrsSPLY AS DECIMAL(8,4))) - 1) * 100


-Hemanth Gudela

dnoeth 4628 posts Joined 11/04
05 Feb 2015

What's the datatypes of the operands?
The easiest solution is to change the percentage calculation to multiply first and then divide:
(100*74.06 / 72.09 - 100) 
Or cast the first operand to a FLOAT or NUMBER and finally cast back to a decimal.


ssi112 5 posts Joined 08/11
10 Feb 2015

Hey guys, thanks for the replies. Both of your suggestions worked, but Dieter's 1st solution was the easist to implement.
Thanks again!

ssi112 5 posts Joined 08/11
10 Feb 2015

I forgot to mention that the attributes of the datatypes used in the calculation are DECIMAL(18,2) NOT NULL.

You must sign in to leave a comment.