Issue with Number data type in Teradata 14.0

Database

Issue with Number data type in Teradata 14.0

I am migrating a table from Oracle to TD 14.0 and using it for further transformations.

After migrating it to TD, I have ran minus on both TD & Oracle table data (which is copied TD by some third party tool).

Query:

Select col1 from table 1 minus select col1 from table 2;

Output:

0.00

Data type col1 table1 is - Number(in TD) & col1 in table 2 number(10,4) (as per Oracle - in TD)

but when I manually check the result both are same and when I have applied with trim then it works fine (no records shown in minus).

Select trim(col1) from table 1 minus select trim(col1) from table 2;

How does trim make difference here ?

I know number datatype is TD ia variable in length and adjust its precision as per the data. Even thought it shows only 2 decimal points in precision in select but internally it stores the complete precision, when u cast it you will get all the precision.

And I have replaced NUMBER data type with DECIMAL then it works fine, only problem is with NUMBER( I need to apply trim to compare the resultset)

Please help me out

1 REPLY
Enthusiast

Re: Issue with Number data type in Teradata 14.0

Hi Suresh,

In Teradata, We don't have NUMBER datatype prior to TD 14 instead we have Decimal Datatype which is more equivalent to Number datatype in Oracle.

AS you  have mentioned,the Datatype in Oracle is Number(10,4). So its equivalent in Teradata is Decimal(14,4) or NUMBER(14,4). If we use this Datatype, therewon't be any problem in Data Matches with the Oracle.

In Teradata, 

Decimal(n,m) represents,

m-  Represents the Scale(TotalNumber),

n - the Precision.

The Same is the case for Number(n,m). Themajor difference is that the Decimal is fixed length whereas the Number is Variable length depends on the exponent & Mantissa.

You can check the length of the Number datatype using CHAR_LENGTH(COL) or LENGTH(COL) of exactly whats the length that used for the NUMBER column.

Thanks & Regards,

Adharssh.