0
813
Question Oleksandr Kyrylov · Feb 2, 2022

ALTER TABLE ALTER COLUMN RENAME Syntax

#InterSystems IRIS #SQL

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.'
Discussion (12)0
Log in or sign up to continue

Comments

Eduard Lebedyuk · Feb 2, 2022

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

Maybe you have a semicolon after your query?

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
Vitaliy Serdtsev  Feb 3, 2022 to Oleksandr Kyrylov

Given (IRIS 2021.2):

<FONT COLOR="#000080">Class fixxer.decars Extends %Persistent </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">DdlAllowed</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlRowIdName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">UniqueIdentifier </FONT><FONT COLOR="#000000">]
{

</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iUnq On (brand, MaxSpeed, color) [ </FONT><FONT COLOR="#000080">IdKey</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">PrimaryKey</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">Unique </FONT><FONT COLOR="#000000">];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">brand [ </FONT><FONT COLOR="#000080">SqlColumnNumber </FONT><FONT COLOR="#000000">= 2 ];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">MaxSpeed;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">color;
}</FONT>
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:

<FONT COLOR="#000080">Class fixxer.decars Extends %Persistent </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">DdlAllowed</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlRowIdName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">UniqueIdentifier </FONT><FONT COLOR="#000000">]
{

</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iUnq On (brand, MaxSpeed, color) [ </FONT><FONT COLOR="#000080">IdKey</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">PrimaryKey</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">Unique </FONT><FONT COLOR="#000000">];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">brand [ </FONT><FONT COLOR="#000080">SqlColumnNumber </FONT><FONT COLOR="#000000">= 2 ];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">MaxSpeed;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">color [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">COLOR2 </FONT><FONT COLOR="#000000">];
}</FONT>

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.

0
Oleksandr Kyrylov  Feb 3, 2022 to Vitaliy Serdtsev

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
Vitaliy Serdtsev  Feb 3, 2022 to Oleksandr Kyrylov

After calling

alter table fixxer.decars alter column color rename color2
it became
<FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">color </FONT><FONT COLOR="#000080">As %Library.String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">MAXLEN </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">500</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlColumnNumber </FONT><FONT COLOR="#000000">= 5, </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">COLOR2 </FONT><FONT COLOR="#000000">];</FONT>
It looks like something was fixed in 2021.2.
0
Oleksandr Kyrylov  Feb 3, 2022 to Vitaliy Serdtsev

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
Vitaliy Serdtsev  Feb 3, 2022 to Oleksandr Kyrylov
I also noticed that in IRIS 2020.1 documentation is nothing about 'ALTER COLUMN RENAME' syntax.
Yes, check alter-column-action ::=2020.12021.1
0
Benjamin De Boe  Feb 3, 2022 to Vitaliy Serdtsev

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

0
Vitaliy Serdtsev  Feb 3, 2022 to Benjamin De Boe

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
Vitaliy Serdtsev  Feb 3, 2022 to Oleksandr Kyrylov

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)

0
Oleksandr Kyrylov  Feb 3, 2022 to Vitaliy Serdtsev

Yes, I decided to use that approach. Thank you

0