Extending thread a little bit.
In oracle we have datatype as NUMBER without any precision and scale. So it can accept large values i.e. around 38 digits and 14 decimals.
So for these, what is the equivalent in teradata?
I tried with FLOAT but it is rounding off upto 2 decimals. Currently we gave DECIMAL with 22 digits and 7 decimals but in future if large values will come into warehouse then it will fail.
Any suggestions please.
in TD14 there's a new NUMBER datatype similar to Oracle's.
FLOAT is not rounding to two decimals, this is just a display problem: you probably used SQL Assistant for testing and there's an option to specify "Number of decimal places to display for Float columns"
But Float has only 15 digits precision.
Otherwise DECIMAL allows up to 38 digits.
We are migrating to TD13.10.
We are thinking to replace NUMBER as DECIMAL(38,14) in teradata, is it good idea to replicate same as Oracle?
or should we first analyse data for max precisions and scale and then take decisions about this?
Of course you should analyse the actual data, the one-size-fits-all approach of NUMBER is for lazy Oracle programmers who don't want to think about the data.
And don't forget to analyse the Oracle DATE columns. In Teradata DATE, TIME and TIMESTAMP are available, usually most Oracle DATEs don't use the time part :-)
Thank you very much for quick response Dieter,
Regarding DATE columns, yes we have already addressed that one by making Oracle DATE columns(Has date, time and AM/PM ) as TIMESTAMP(24 hrs) in teradata .
Thanking you ,
have the NUMBER datatype some default precision and scale? I thought it saves any number as accurate as possible up to 38 significant digits. But it evidently is not so unlike limited datatype NUMBER(38,6). It looks that NUMBER keeps only 15 significant digits as like as FLOAT.
SELECT CAST (12345678901234567.8901 AS NUMBER(38,6)) AS limited_number
,CAST (12345678901234567.8901 AS NUMBER) AS unlimited_number
But I don’t want to limit decimal places if I need not. Is there any other possibility how to double NUMBER precision than by limiting it?
Here is what the Teradata Database Reference / SQL Data Types and Literals / Chapter 3 Numeric Data Types / NUMBER Data Type section says:
"Calculations for the NUMBER data type are guaranteed to 38 digits of precision, but are often performed with 39 or 40 digits of precision. If precision and scale are not limited, NUMBER data will retain the full precision and scale of the calculations."
Your example is flawed because you are casting FLOAT literals to the NUMBER data type, and FLOAT literals are limited to the precision of the FLOAT data type.
Instead, you should try casting string literals to NUMBER:
select cast('123456789012345678901234567890' to number)