Alter Table

Database
Enthusiast

Alter Table

My table looks like this...

CREATE SET TABLE FIRST_EX.tab1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
eno INTEGER,
ename CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( eno );

When i try to alter the table definition( ename char(6) to char(10))
I get a Faliure message.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
alter table tab1 add ename char(10);

alter table tab1 add ename char(10);
*** Failure 3558 Cannot alter the specified attribute(s) for ENAME.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
sel * from dbc.errormsgs where errorcode = 3558;

sel * from dbc.errormsgs where errorcode = 3558;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 2 seconds.

ErrorCode ErrorText
--------- -----------------------------------------------------------------
3,558 Cannot alter the specified attribute(s) for %VSTR.

Is it because i have insfficient privillages?
But i created the table and also able to drop the column.
I am running TD Demo V2R5.1
5 REPLIES
Enthusiast

Re: Alter Table

The change from char(6) to char(10) is not supported.For Char columns only change allowed is conversion fron Non-CS to CS.

refer DDL manual(TTU 8.2), ALTER TABLE syntax page#70 for more details.
Enthusiast

Re: Alter Table

With reference to SQL DDL Manual.
Chapter 1, Page 40:
"The Teradata ALTER TABLE statement offers many extensions not offered by ANSI SQL. For example, changing column attributes in ANSI SQL is restricted to setting or dropping a default clause, which is not the case in Teradata SQL".

HELP SESSION;
....on my system shows me that the Transaction Semantics are 'Teradata'.
Does this mean I am running in Teradata mode as opposed to ANSI mode?

I did think there was some type of allowance for altering char & varchar column sizes as long as you were increasing them. This would make sense as decreasing them would not protect the data.

Just a thought.
Junior Contributor

Re: Alter Table

You can change the datatype of a column as long as the physical storage remains the same,
e.g. VarChar(5) -> Varchar(10), but not Varchar(10) -> Varchar(9)
or Dec(5,0) -> Dec(9,0), nut not Dec(9,0) to Dec(12,0) or Dec(9,1)

And this is regardless of your session mode (although there are lots of differences in Teradata vs. ANSI mode)

Dieter
Enthusiast

Re: Alter Table

Can you post the exact syntax.
I have not been able to do this with varchar or char!
Enthusiast

Re: Alter Table

As Dieter mentioned, you can't alter a column, if the changes the physical storage, which implies that you can't alter CHAR(n)

to alter varchar(n), you can use the same sytnax for adding a new column (yeah, I know, kind of spooky ! ;) )

-- create a table
ct (id integer, col1 varchar(10));

-- increase the length of the varchar column
alter table mytab1 add col1 varchar(20);