Rounding: Result of division being forced to .000

Database
Enthusiast

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

Tags (2)
4 REPLIES
Enthusiast

Re: Rounding: Result of division being forced to .000

Hi,

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

Regards

Junior Contributor

Re: Rounding: Result of division being forced to .000

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.

Enthusiast

Re: Rounding: Result of division being forced to .000

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

Thanks again!

Enthusiast

Re: Rounding: Result of division being forced to .000

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