Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2015
09:21 AM

02-05-2015
09:21 AM

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

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2015
11:16 AM

02-05-2015
11:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2015
11:29 AM

02-05-2015
11:29 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-10-2015
09:56 AM

02-10-2015
09:56 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-10-2015
10:08 AM

02-10-2015
10:08 AM

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.