Oracle numeric data type equivalent on Teradata.

Database

Oracle numeric data type equivalent on Teradata.

I understand Decimal data type on Teradata which define fix length decimal points.
Teradata:
Decimal (15.2) meaning maximum 15 characters with fix length 2 decimal points.

Oracle:
Number with length 38 meaning maximum 38 digit numerics with number of decimal points vary from 0 to 38.

Decimal data type on Teradata is define decimal points in fix length, however on Oracle numeric data type behave decimal point in variable. >> in this case decimal data type can NOT be equivalent to number data type.

How to handle/map Oracle numeric data type to any Teradata data type?

SUP
8 REPLIES
Enthusiast

Re: Oracle numeric data type equivalent on Teradata.

Oracle is using a float data type - so use a float data type if you want the same features.
One problem with float is that you can get loss or approximation of significant digits if you have very long number. (On both systems.)

I would always use the "best fit" numeric data type - Integer if it is a whole number, Decimal if the number will fit, Float when I have no idea of the range or size of number coming in.
Enthusiast

Re: Oracle numeric data type equivalent on Teradata.

Hi,

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.

Regards,

Ashish

Junior Contributor

Re: Oracle numeric data type equivalent on Teradata.

Hi Ashish,

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.

Dieter

Enthusiast

Re: Oracle numeric data type equivalent on Teradata.

Thanks Dieter,

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?

Regards,

Ashish

Junior Contributor

Re: Oracle numeric data type equivalent on Teradata.

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

Dieter

Enthusiast

Re: Oracle numeric data type equivalent on Teradata.

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 ,

Ashish

Re: Oracle numeric data type equivalent on Teradata.

Hi,

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        

;

limited_number                                  unlimited_number

12345678901234567,890100            12345678901234600,000000000

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?

Zbynek

ZST
Teradata Employee

Re: Oracle numeric data type equivalent on Teradata.

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)