Automatically Rounding

Database
Enthusiast

Automatically Rounding

Hi,

i am fireing same query in oracle and in teradata on same table( source and target tables).

when i am fireing the query in oracle i am getting values like
A
45.98
B
96.69
C
97.06

but when i am fireing the same query in teradata i am getting values like this

A
46.00
B
96.70
C
97.10

I want the same values in teradata also.

this is the query i am using in both the databases.

SELECT
ROOM_id,
SUM(CLOCK_COUNT)/SUM(MONTH_COUNT)*100 AS A,
SUM(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT)*100 AS B,
SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT)*100 AS C
FROM
TABLE NAME
Group By 1

kindly help me to resolve the issue ...

thxs,
Moras
7 REPLIES
Enthusiast

Re: Automatically Rounding

It certainly comes from the TYPE of your columns...

INTEGER has no decimals and the result of DECIMAL(x, m) / DECIMAL(y, n) is DECIMAL(y, max(m,n))...

So if you want a DECIMAL(z,2) result, CAST one of your values to DECIMAL(z,2)...
Enthusiast

Re: Automatically Rounding

Hi,

i have tried with casting on of the decimals as (z,3) but i am getting the same results, the result is rounding off, is there any other way to get the result without rounding off.
Enthusiast

Re: Automatically Rounding

I presume you are querying from queryman. in that case go to tools->options and in the answerset tab look from something like "number of decimal places to display for float".

Re: Automatically Rounding

Looks to me like the problem is your * 100 at the end. The casting to decimal(x,3) or decimal(x,2) is giving 2 decimal precision prior to the multiplication. Also, if one of the two part to the division equation = float, the result will = float data type. cast both parts of the division equation separately, and then you will get the appropriate answer.

ie
cast(number1 as decimal(18,4)) / cast(number2 as decimal(18,4)) * 100

In this case, the result of the division problem will have a precision of 4 decimal places. When multiplying by 100 you are basically shifting the precision 2 places to the left with a result of 2 decimal precision. If you don't wish to see the trailing zeros wrap the whole thing in another cast( as decimal(18,2))
Junior Contributor

Re: Automatically Rounding

Hi Morashi,
according to Standard SQL any calculation involving decimals are rounded after each step, so simply reorder the calculations:

SELECT
ROOM_id,
100*SUM(CLOCK_COUNT)/SUM(MONTH_COUNT) AS A,
100*(CLOCK_UPTIME_COUNT)/SUM(MONTH_COUNT) AS B,
100*SUM(CONTRACT_HOUR_COUNT)/SUM(CONTRACT_COUNT) AS C
FROM
TABLE NAME
Group By 1

Dieter

Re: Automatically Rounding

Hi Dieter,

 

Does the 100 need to be multiplied to the left instead of right to avoid the rounding at the tens in the example below?

 

Regards,

Srini

Junior Contributor

Re: Automatically Rounding

Yep, the rule of thum is: Multiply first, then divide