Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-04-2010
04:13 AM

01-04-2010
04:13 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-05-2010
03:19 AM

01-05-2010
03:19 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-21-2013
10:16 PM

01-21-2013
10:16 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-21-2013
11:41 PM

01-21-2013
11:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-22-2013
01:09 AM

01-22-2013
01:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-22-2013
01:52 AM

01-22-2013
01:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-22-2013
04:54 AM

01-22-2013
04:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2014
07:48 AM

11-05-2014
07:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-06-2014
09:11 AM

11-06-2014
09:11 AM

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)