Database

## 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 4
Highlighted

## 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

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

## 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!

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