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

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

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

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

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

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