Question
Oleksandr Kyrylov · Feb 2, 2022

ALTER TABLE ALTER COLUMN RENAME Syntax

Hi everyone, i cant understand what is wrong in my query: 

ALTER TABLE MyNamespace.MyTable ALTER COLUMN CurrentColumnName RENAME NewColumnName

I just want to rename column name using sql.

I could not find any life example using this syntax.

SQLCODE for this query is -25:

-25 Input encountered after end of query

Quote from documentation:

  • Rename the column using the syntax ALTER TABLE tablename ALTER COLUMN oldname RENAME newname. Renaming a column changes the SQL field name. It does not change the corresponding persistent class property name. ALTER COLUMN oldname RENAME newname replaces oldfield name references in trigger code and ComputeCode.'
0
0 271
Discussion (12)0
Log in or sign up to continue

Can't reproduce. Can you post an example please?

Maybe you have a semicolon after your query?

Example:

I have table named fixxer.decars, i want to rename column "color" to "color2"

I tried to use the query above and met the error

Given (IRIS 2021.2):

Class fixxer.decars Extends %Persistent DdlAllowedSqlRowIdName UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKeyPrimaryKeyUnique ];
Property brand [ SqlColumnNumber = 2 ];
Property MaxSpeed;
Property color;

}
USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: >.
Enter , 'q' to quit, '?' for help.
[SQL]USER>>select * from fixxer.decars
1.      select * from fixxer.decars
 
UniqueIdentifier        brand   MaxSpeed        color
 
0 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0020s/5/140/0ms
          execute time(s)/globals/cmds/disk: 0.0004s/1/719/0ms
                          cached query class: %sqlcq.TEST.cls4
---------------------------------------------------------------------------
[SQL]USER>>alter table fixxer.decars alter column color rename color2
2.      alter table fixxer.decars alter column color rename color2
 
0 Rows Affected
statement prepare time(s)/globals/cmds/disk: 0.0153s/2 113/13 157/0ms
          execute time(s)/globals/cmds/disk: 0.1492s/52 206/467 790/0ms
                          cached query class: %sqlcq.TEST.cls5
---------------------------------------------------------------------------
[SQL]USER>>select * from fixxer.decars
3.      select * from fixxer.decars
 
UniqueIdentifier        brand   MaxSpeed        COLOR2
 
0 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0741s/38 529/179 560/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/1/719/0ms
                          cached query class: %sqlcq.TEST.cls4
---------------------------------------------------------------------------
[SQL]USER>>

Now:

Class fixxer.decars Extends %Persistent DdlAllowedSqlRowIdName UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKeyPrimaryKeyUnique ];
Property brand [ SqlColumnNumber = 2 ];
Property MaxSpeed;
Property color [ SqlFieldName COLOR2 ];

}

PS: by the way Studio highlights the error in the following code

&sql(alter table fixxer.decars alter column color rename color2)

But the compilation goes without errors.

My IRIS version is 2020.1. 

I will be grateful if you see any significant differences between my example and yours that cause this error. My Persistent class (this class created using 'CREATE TABLE'):

// 
Class Fixxer.DEcars Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {yploskyi}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = DEcars ]
{ 
Property UniqueIdentifier As %Library.String(MAXLEN = 30) [ SqlColumnNumber = 2 ]; 
Property brand As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 3 ];
Property maxspeed As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 4 ]; 
Property color As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 5 ]; 
Parameter USEEXTENTSET = 1; 
/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement. Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
}

After calling

alter table fixxer.decars alter column color rename color2

it became

Property color As %Library.String(MAXLEN 500) [ SqlColumnNumber = 5, SqlFieldName COLOR2 ];

It looks like something was fixed in 2021.2.

I  think so too. I also noticed that in IRIS 2020.1 documentation is nothing about 'ALTER COLUMN RENAME' syntax. May be there is another way to rename column name using SQL but i cant find it. Thanks for response.

I also noticed that in IRIS 2020.1 documentation is nothing about 'ALTER COLUMN RENAME' syntax.

Yes, check alter-column-action ::=
2020.1
2021.1

This is indeed a new piece of ALTER TABLE syntax we introduced between 2020.1 and 2021.1, so nothing wrong with your 2020.1 instance

So, the author is not lucky and in its version this feature is not available.
It seems you have the wrong addressee, I have 2021.2 :)

I won't be able to check for version 2020.1 since I have 2021.2.

Try to make a DROP COLUMN and then ADD COLUMN (of course according to the documentation for your version)