1

Can you please help me concerning this matter (I didn´t found it in the Teradata documentation, which is honestly little overwhelming): My table had this column -BAN DECIMAL(9,0)-, and now I want to change it to - BAN DECIMAL(15,0) COMPRESS 0.- How can I do it? What does COMPRESS constraint 0. or any other mean anyway? I hope this is possible, and I don`t have to create a new table and then copy the data form the old table. The table is very very big - when I do COUNT(*) form that table I get this error: 2616 numeric overflow occurred during computation

Dantes
  • 2,693
  • 5
  • 24
  • 33

3 Answers3

2

The syntax diagram for ALTER TABLE doesn't seem to support directly changing a column's data type. (Teradata SQL DDL Documentation). COMPRESS 0 compresses zeroes. Teradata supports a lot of different kinds of compression.

Numeric overflow here probably means you've exceeded the range of an integer. To make that part work, just try casting to a bigger data type. (You don't need to change the column's data type to do this.)

select cast(count(*) as bigint) 
from table_name;
Mike Sherrill 'Cat Recall'
  • 82,047
  • 16
  • 110
  • 161
  • So basically, you suggest to create new table and carry the data from the existing table to the newly created table? There´s no other way? – Dantes Aug 10 '12 at 12:30
  • You can add a new column instead of a new table, copy the data, drop the old column, and rename the new column. (Syntax is in the DDL documentation.) I think I've seen discussions on the Teradata user forums that say creating a new table is often faster, though. – Mike Sherrill 'Cat Recall' Aug 10 '12 at 13:24
2

You asked three different questions:

  1. You cannot change the data type of a column from DECIMAL(9,0) to DECIMAL(15,0). Your best bet would be to create a new column (NEW_BAN), assign values from your old column, drop the old column and rename NEW_BAN back to BAN).

  2. COMPRESS 0 is not a constraint. It means that values of "zero" are compressed from the table, saving disk space.

  3. Your COUNT(*) is returning that error becasue the table has more than 2,147,483,647 rows (the max value of an INTEGER). Cast the result as BIGINT (as shown by Catcall).

And I agree, the documentation can be overwhelming. But be patient and focus only on the SQL titles for your exact release. They really are well written.

BellevueBob
  • 9,152
  • 5
  • 27
  • 51
  • Hi Bob! Thanks for the reply. i like the suggestion about adding a new column and copying data in that new column. what I would like to know is it possible to keep the same order of columns i.e. Can I place the new column on the same spot where the old column is, or this column can only be at the end of the table? – Dantes Aug 13 '12 at 07:23
1

You can not use ALTER TABLE to change the data type from DECIMAL(9,0) to DECIMAL(15,0) because it cross the byte boundary required to store the values in the table. For Teradata 13.10, see the Teradata manual for SQL Data Definition Language Detailed Topics pages 61-65 for more details on using ALTER TABLE to change column data types.

Rob Paller
  • 7,506
  • 23
  • 23