New post
Question
Oleksandr Kyrylov · Feb 2, 2022

ALTER TABLE ALTER COLUMN RENAME Syntax

#SQL #Caché #InterSystems IRIS

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
Eduard Lebedyuk · Feb 2, 2022

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

Maybe you have a semicolon after your query?

1 0
Oleksandr Kyrylov · Feb 2, 2022

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

0 0
Vitaliy Serdtsev · Feb 3, 2022

Given (IRIS 2021.2):

Class fixxer.decars Extends %Persistent [ DdlAllowed, SqlRowIdName = UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKey, PrimaryKey, Unique ];
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 [ DdlAllowed, SqlRowIdName = UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKey, PrimaryKey, Unique ];
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.

1 0
Oleksandr Kyrylov · Feb 3, 2022

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 ];
}
0 0
Vitaliy Serdtsev · Feb 3, 2022

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.

1 0
Oleksandr Kyrylov · Feb 3, 2022

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.

0 0
Vitaliy Serdtsev · Feb 3, 2022

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

1 0
Benjamin De Boe · Feb 3, 2022

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

1 0
Vitaliy Serdtsev · Feb 3, 2022

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 :)

0 0
Vitaliy Serdtsev · Feb 3, 2022

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)

1 0