Question
Sreeram Makam · Oct 8, 2019

Cache DDL syntax to rename a Table

Hi All, I am a Data Architect and am trying a method of deploying alter statements onto Cache DB.

My Data Modeling tool generates alters as this.. So question is if the Cache has a way to rename table? If so what is it?

ALTER TABLE <SchemaName>.GROUP_TYPE_NODES RENAME TO GROUP_TYPE_10082019140110000
;

This is giving error.

SQL Error [25] [37000]: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (IDENTIFIER) encountered after end of query^ALTER TABLE xxx.GROUP_TYPE_NODES RENAME>]
 

#Beginner #Data Model #SQL #Caché
00
1 0 4 288
Log in or sign up to continue

Replies

Robert Cemper · Oct 8, 2019

But you can rename a table using Studio or Atelier.

20
Otto Medin · Dec 10, 2020

...or using a stored procedure along these lines:

ClassMethod RenameTable(oldName As %String, newName As %String) As %String [ SqlProc ]
{
   try {
      &sql(select %ID into :className
         from %Dictionary.ClassDefinition
         where SqlTableName = :oldName
      )
      if SQLCODE set status = "Error: Table '" _ oldName _ "' not found." quit

      set classDef = ##class(%Dictionary.ClassDefinition).%OpenId(className)
      set classDef.SqlTableName = newName
      set saveStatus = classDef.%Save()
      set status = $case(saveStatus, 1: "OK", : "Error: " _ $system.Status.GetErrorText(saveStatus))
   } catch {
      set status = "Error: " _ $zerror
   }
   return status
}

Note: This doesn't handle the special case where there is no 'SqlTableName' defined.

00
Sreeram Makam · Oct 8, 2019

Yes I tried but no success 

ALTER TABLE SD.GROUP_TYPE_NODES GROUP_TYPE_10082019140110000
;

SQL Error [25] [37000]: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (IDENTIFIER) encountered after end of query^GROUP_TYPE_10082019140110000>]
 

not sure of how to use Studio.

00