How to rename a view with dependent views

Discuss the database features

How to rename a view with dependent views

Postby CTBarbarin » Wed Jan 09, 2019 9:09 am

(Split HSQLDB database v. 2.3.2)

Hi all,

is it possible to rename a view that has dependent views?

Imagine this:
  • Table named tblData
  • View named vwData_TB based on table tblData
  • View named vwData_RB based on view vwData_TB

When I tried to rename vwData_TB, I received the following error:

1: dependent objects exist: PUBLIC.VWDATA_RB in statement
[ALTER TABLE "VWDATA_TB" RENAME TO "DELETE_VWDATA_TB"]


I need to make some changes to (recreate) vwData_TB, but I don't want to delete the original until I know the new version is working.

Thanks for any help anyone can provide,

CTB

OpenOffice 4.1.2 on Windows 8.1
CTBarbarin
 
Posts: 21
Joined: Thu Apr 02, 2015 6:24 am
Location: Phoenix, AZ

Re: How to rename a view with dependent views

Postby UnklDonald418 » Thu Jan 10, 2019 9:39 pm

Since views are created from queries, I'm assuming you didn't save copies of the queries used to create the views (a recommended practice when using a split database), otherwise you can rely on the queries for backup and first delete "vwData_RB" and then "vwData_TB".

Lacking the queries there are a couple of options.
The easiest would be to simply make a backup copy of the .odb file. If your edits don't work out you have the backup file to return to the original state.

For the other option, look in the database directory of your split database for the .script file. Open the .script file with Windows WordPad and locate the CREATE VIEW statements. Don't try to edit the .script file because you could lose the connection to your tables, but you can copy those statements into the clipboard. I would use Create Form in Design View and paste them into a form document that you can save for later use if needed. Since the form document is part of the database you don't need to search you computer for a file containing the saved statements.
If you find you need to recreate those views, use Tools>SQL to execute the saved CREATE VIEW statements.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How to rename a view with dependent views

Postby Sliderule » Fri Jan 11, 2019 1:13 am

Always, always, always, make a backup of your working database - the four files:
  1. *.backup
  2. *.data
  3. *.properties
  4. *.script
before making any changes. :super:

To see the Select statement(s) that comprise your current VIEW, run the Query below . . . and . . . store ( for safekeeping in case you need it again ) the contents of the column VIEW_DEFINITION

Code: Select all   Expand viewCollapse view
Select
   INFORMATION_SCHEMA.VIEWS.TABLE_NAME, -- This is the name of your View
   INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION  -- This is the DDL (Data Definition Language ) to define the View
From INFORMATION_SCHEMA.VIEWS
Where INFORMATION_SCHEMA.VIEWS.TABLE_NAME = 'vwData_TB'  -- This will display the information for your desired View

Explanation:
  1. The above table INFORMATION_SCHEMA.VIEWS is a standard SQL table in your Relational Database, HSQL

  2. Use the command of your database back-end, from the Menu: Tools -> SQL... . . . per HSQL documentation . . .
    1. ALTER VIEW

      alter view statement

      <alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

      Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there
      are database objects such as routines or
      views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.

    2. Therefore, you should use a statement something like, and, change the words YOUR MODIFIED SELECT STATEMENT(S) HERE, to your desired code:

      Code: Select all   Expand viewCollapse view
      ALTER VIEW "vwData_TB" AS YOUR MODIFIED SELECT STATEMENT(S) HERE

  3. Just as an FYI (For Your Information), if you want to know the Tables used in a View, run the following Query:

    Code: Select all   Expand viewCollapse view
    Select
       INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME,
       GROUP_CONCAT('"' || INFORMATION_SCHEMA.VIEW_TABLE_USAGE.TABLE_NAME || '"') as TABLE_NAME
    From INFORMATION_SCHEMA.VIEW_TABLE_USAGE
    Where INFORMATION_SCHEMA.VIEW_TABLE_USAGE.TABLE_SCHEMA = CURRENT_SCHEMA
    Group By INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME
    Order By UPPER(INFORMATION_SCHEMA.VIEW_TABLE_USAGE.VIEW_NAME)
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests