DECIMAL datatype behavior while doing calculations in Teradata

Scenario: Look at the following result we get in Teradata. when multiplying two decimal the result data has double decimal precision

\n\n
SELECT TYPE(CAST (2.3 AS DECIMAL(18,4)) * CAST (2.3 AS DECIMAL(18,4))  )\n
\n\n

The result is:

\n\n
DECIMAL(18,8)\n
\n\n
  • Teradata's basic rules are:\nWhen you add / substract / divide DECIMALs the resulting fractional precision is the greater of both operands, e.g. dec(10,2) + dec(10,4) = dec(xx,4)
  • But when you multiply the fractional digits are added, e.g.dec(10,2) * dec(10,4) = dec(xx,6)
  • And then there's the most important rule, people tend to forget:\nAfter each calculation the result is rounded to this precision

Example:

\n\n\n\n\n\n
sel 2.0/3.00 * 100, 100*2.0/3.00;\n\n    *** Query completed. One row found. 2 columns returned.\n    *** Total elapsed time was 1 second.\n\n ((2.0/3.00)*100)   ((100*2.0)/3.00)\n-----------------  -----------------\n            67.00              66.67\n
\n\n

nVector

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds