Store Negative decimal value into teradata datatype

Database
N/A

Store Negative decimal value into teradata datatype

Hi ,

Could you please let me know what datatype to use to store below value., i have negative decimal value as well, i have tried using decimal,smallint,byteint,integer,bigint, but no luck.

26.66

67.1

0.0

-29.71

-1.0

19.98

7 REPLIES
N/A

Re: Store Negative decimal value into teradata datatype

There are no unsigned numeric data types in Teradata, so a DECIMAL(xx,2) seems to be correct.

If there's no visible minus sign it's a display issue in your client.

N/A

Re: Store Negative decimal value into teradata datatype

Hi Dieter,

i have two tables Table A and tablle B , where Table A has 26.66

67.1

0.0

-29.71

-1.0

19.98 having datatype as varchar.

now i want to load data from table A to Table B(where Table B has decimal dataype ), iam getting Bad format error.

below query i have used.

insert into table b

select cast(de_ma_value as decima(5,2) from Table a

Re: Store Negative decimal value into teradata datatype

Hi Shabeena,

I don't see any issue on that - 

create volatile table a
(
col1 varchar(20)
) on commit preserve rows;

insert INTO a values ('26.66');
insert INTO a values ('67.1');
insert INTO a values ('0.0');
insert INTO a values ('-29.71');
insert INTO a values ('-1.0');
insert INTO a values ('19.98');

create volatile table b
(
col1 decimal(5,2)
) on commit preserve rows;

insert INTO b select CAST(col1 as decimal(5,2)) from a;

sel * from b;
N/A

Re: Store Negative decimal value into teradata datatype

Thanks Sakthi

its working fine :)

N/A

Re: Store Negative decimal value into teradata datatype

If there's some bad data you might use TO_NUMBER(col) which returns NULL instead of an error message, e.g. WHERE TO_NUMBER(col) IS NULL shows those rows...

Re: Store Negative decimal value into teradata datatype

Thanks Dieter!!

As Dieter said you can use to_number() as shown below:

insert INTO b select CAST(coalesce(to_number(col1),0.0) as decimal(5,2)) from  a;

Re: Store Negative decimal value into teradata datatype

Hi.

Beware of certain behaviour of TO_NUMBER():

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT TO_NUMBER('1,000,000.00');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

TO_NUMBER('1,000,000.00')
----------------------------------------
(null)

Cheers.

Carlos.