0

I want to demonstrate the problem with the help of an example.

Accurate division: 7299 / 48 = 152.0625 However, in Teradata all precision is lost when querying for select 7299/48 = 152

We have to apply a cast for precision, like: select cast(7299/48 as decimal(8,5))

Assuming that my data column has a length of 8. Here I cannot provide value greater than 8 in the first argument. In the second argument (number of digits after decimal point), at max I can provide 5 because the result yields 3 digits in the numeric part and as a remainder we get 5 (8-3).

I'm asking this because I can go into troubles if I apply the same cast to: select 72990/48 = 1520.625 because here the numeric part has 4 digits and I can only supply 4 in the second argument.

Is there a way that I can specify only the first argument 8 (equaling to the length of the column) and get the result with complete precision?

flag
An additional note for casting in decimal: Max precision can be achieved like cast(X as decimal(18, Y)), where Y is the number of digits after the decimal. – Monis Iqbal Jan 19 at 10:54

1 Answer

1

The easiest way to get the precision you are looking for is to add the number of decimal places you are looking to the divisor:

SELECT 7299 / 48.0000; 
SELECT 7299.0000 / 48;

You can either change the data type in the table for one of the operands from integer to DECIMAL(m,n) or modify your SQL statement as follows:

SELECT ColA / (ColB * 1.00000)
SELECT (ColA * 1.0000) / ColB

Chapter 3 of the SQL Reference: Functions and Operators explains the results based on the operands involved in the operation. The table listed there explains how and why this works.

link|flag
Cool way of achieving the result. Do you think this will be faster than the cast as decimal? Sorry for the late comment. I was busy in other stuff so didn't have time to test things. – Monis Iqbal Jan 19 at 10:52
It takes advantage of the implict casts in Teradata. You could see if the EXPLAIN has an explicit CAST included in it or not. It seems to be pretty quick and is the method I have used for years. – Rob Paller♦ Jan 22 at 2:46

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.