Calculation Precision issue Oracle Vs Teradata

Database
Fan

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

2 REPLIES
Junior Contributor

Re: Calculation Precision issue Oracle Vs Teradata

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)
Enthusiast

Re: Calculation Precision issue Oracle Vs Teradata

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