All Forums Database
08 Aug 2007
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

leo.issac 184 posts Joined 07/06
08 Aug 2007

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.

marcmc 112 posts Joined 12/05
25 Oct 2007

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.

dnoeth 4628 posts Joined 11/04
25 Oct 2007

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

Dieter

marcmc 112 posts Joined 12/05
26 Oct 2007

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

joedsilva 505 posts Joined 07/05
26 Oct 2007

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 tablect (id integer, col1 varchar(10));-- increase the length of the varchar columnalter table mytab1 add col1 varchar(20);

You must sign in to leave a comment.