All Forums UDA
kumar-4110 8 posts Joined 03/07
28 Mar 2007
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.

joedsilva 505 posts Joined 07/05
28 Mar 2007

There's no such thing as modify on column name, you use the same syntax as ADD columnalter table xxx add emp_name varchar(20)that will make it varchar(20) from (10)

kumar-4110 8 posts Joined 03/07
28 Mar 2007

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.

joedsilva 505 posts Joined 07/05
29 Mar 2007

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.

Maanchaala 2 posts Joined 04/07
17 Apr 2007

It can be achieved by using CAST in TD.Ex:SELECT CAST(first_name AS CHAR(5) UPPERCASE)FROM employeeWHERE department_number = 401;regards,hari

vedamurthy-3684 2 posts Joined 12/06
24 Apr 2007

it can be achived by using,alter table table_nameadd col_name varchar(50);preiously it was varchar(10) now it changes to varchar(30)for datatype char is not supportedby vedamurthy

asadali.khan 11 posts Joined 10/10
01 Nov 2010

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.

Asad Ali Khan

Karam 75 posts Joined 07/09
01 Nov 2010

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.

Aravind_as 1 post Joined 08/12
09 Aug 2012

Hi,

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

Sen_td 20 posts Joined 08/11
14 Aug 2012

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

You must sign in to leave a comment.