SQL Query to modify a column datatype lenght.!!!

UDA
Enthusiast

SQL Query to modify a column datatype lenght.!!!

Hi All,

I need an SQL Query in TD to modify a column datatype length.

something like we do in oracle(say if existing length is varchar(10),i need it to be changed to varchar(20))

alter table xxx modify emp_name varchar(20)

Thanks for your help,
Kumar.
11 REPLIES
Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!


There's no such thing as modify on column name, you use the same syntax as ADD column

alter table xxx add emp_name varchar(20)

that will make it varchar(20) from (10)
Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!

When i try to do this(initial datatype is char(20))

alter table emp add first_name char(30)

it throws an error saying

3558: Cannot alter the specified attribute(s) for first_name.
Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!

You can't alter CHAR(n) types for their length. TD let's you alter the types/lengths of only a small set of data types. You can refer the data types & literals manual for more detail.

Re: SQL Query to modify a column datatype lenght.!!!

It can be achieved by using CAST in TD.

Ex:

SELECT CAST(first_name AS CHAR(5) UPPERCASE)
FROM employee
WHERE department_number = 401;

regards,
hari

Re: SQL Query to modify a column datatype lenght.!!!

it can be achived by using,

alter table table_name
add col_name varchar(50);

preiously it was varchar(10) now it changes to varchar(30)
for datatype char is not supported
by vedamurthy
Teradata Employee

Re: SQL Query to modify a column datatype lenght.!!!

Here is the solution

To Modify the a column type, length or format, we use ADD function in Teradata

ALTER TABLE Table_Name
ADD Existing_Column_Name New_Data_Type(New_Length)

Above query will change the type or length or format of the existing column.

Hope it helps.
Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!

Here are the results while applying above syntax for column's data type change:

VARCHAR ( from VARCHAR(20) TO VARCHAR(22)) - successful
INTEGER ( FROM INTEGER TO DECIMAL(15,0)) - syntax error - 3558
CHAR ( FROM CHAR (5) TO CHAR (7)) - - syntax error - 3558
TIMESTAMP ( FROM TIMESTAMP(0) TO TIMESTAMP(1) )- syntax error - 3558
DECIMAL (FROM DECIMAL(15,0) TO DECIMAL(15,1) )- syntax error - 3558

It shows that the procedure is limited to VARCHAR.

Re: SQL Query to modify a column datatype lenght.!!!

Hi,

I need to change the datatype of a column from time to varchar. Is it possible. ?

Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!

Not through 'Alter' rather create new column with varchar.