All Forums Database
ankitk 2 posts Joined 10/13
01 Sep 2014
Calculation Precision issue Oracle Vs Teradata

Hi Team 

 

While performing calculations I am facing some precision issues between output from Oracle and Teradata.Please Help.

 

-- Oracle

SELECT ROUND((((3.82/23.88)*23.88)/767),2) FROM dual

--0.000

 

 

-- Teradata

SELECT CAST(ROUND(  CAST((( CAST(3.82 AS DECIMAL(18,8))/ CAST(23.88 AS DECIMAL(18,8))) * 23.88 ) / CAST(767 AS DECIMAL(18,5)) AS DECIMAL(18,4)) ,2) AS DECIMAL(18,2))

 

-- 0.01

 

Thanks

Ankit K

dnoeth 4628 posts Joined 11/04
01 Sep 2014

Hi Ankit,
Teradata rounds after every step based on the precision of the datatypes.
There's a rule of thumb, multiply first, then divide, but in your case this will not help:

SELECT 23.88 * 3.82 / 23.88 /767
0.0050

You might increase the precision on one of the operands:

 SELECT CAST(23.88 AS DECIMAL(38,8)) * 3.82 / 23.88 /767
 0.0049804433

But you still might have problems because Oracle doesn't use DECIMALs. In TD14 you might switch to NUMBER to avoid this:

SELECT ROUND((((CAST(3.82 AS NUMBER)/23.88)*23.88)/767),2)

 

Dieter

Raja_KT 1246 posts Joined 07/09
01 Sep 2014

Your second query is Teradata?
In Oracle version 11g, it works.
SELECT CAST(ROUND(  CAST((( CAST(3.82 AS DECIMAL(18,8))/ CAST(23.88 AS DECIMAL(18,8))) * 23.88 ) / CAST(767 AS DECIMAL(18,5)) AS DECIMAL(18,4)) ,2) AS DECIMAL(18,2)) from dual;
.01
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.