2

I have a column with datatype float in Teradata. I want to find the Maximum precision and scale for that column.

Note: My column's scale part has more than 10 digits in most of the places.

Sample Data

123.12321323002
13123213.13200003
33232.213123001

The output I need is Precsion 19 (scale + length of 13123213) and scale is 11 (length of 12321323002) or

8 (length of 13123213), 11 (length of 12321323002).

I tried to find them buy converting the column as varchar and splitting them based on the '.' and make the integer and fractional part as 2 columns and then finding the max length of 2 columns. But when I'm select the data, Teradata rounds off the scale part. So after that, if I convert them as char, I'm getting lesser value for scale part.

For example:

org data: 1234.12312000123101

data when I select from Teradata: 1234.12312000123

Gopirengaraj C
  • 31
  • 1
  • 8

2 Answers2

0

This is a bit long for a comment.

Teradata uses the IEEE format for real/float values. This gives 15-17 digits of precision. Alas, you need 19 digits, so the values you want are not being stored in the database. You cannot recover them.

What you can do is fix the database, so it uses numeric/decimal/number. This supports what you want: NUMERIC(19, 11). You would then need to reload the data so it is correctly stored in the database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • TL;DR - never use float, the problems outweigh any possible benefits. – Andrew Dec 29 '20 at 14:17
  • @Andrew . . . Floats have a place when the *range* of values exceeds anything that could be represented in a fixed-length format. In real world databases, that is very, very rarely needed. In business databases, perhaps never. – Gordon Linoff Dec 29 '20 at 14:19
  • Fair enough. I just can't stand the un-predictability, lack of guaranteed accuracy with floats. But maybe that's just because I'm a little OCD... – Andrew Dec 29 '20 at 14:41
0

When you need high precision without predefined scale simply switch to the NUMBER datatype, which is a mixture of DECIMAL and FLOAT.

Exact numeric, at least 38 digits precision, no predefined scale, range of 1E-130 .. 1E125.

Float on steroids :-)

dnoeth
  • 59,503
  • 4
  • 39
  • 56