Creating Views
In DatabaseSpy you can create a view from a SELECT statement in the SQL Editor, or copy the definition of an existing view via the context menu in the Online Browser. This way, you have frequently used complex queries stored in database and can later execute them with a single mouse click or use them as a basis for more fine-tuned queries.
The SQL syntax in the instructions below may vary depending on the database kind you are working with.
| Note: | You may have to refresh the data source to view a newly created view in the Online Browser. |
Creating Views in the SQL Editor
To create a view in SQL Editor:
1.Write or generate a SELECT statement in the SQL Editor (for example, right-click a table in the Online Browser, and select Show in new SQL Editor | SELECT from the context menu).
2.Right-click in the SQL Editor window and select Create View As from the context menu. Alternatively, you can also select the menu option SQL Refactoring | Create View As. The statement CREATE VIEW [View1] AS is automatically inserted in front of the query and the view name View1 is automatically highlighted.
3.Change the view name to a more descriptive one. If a view with this name already exists, an error will occur.
4.Click the Execute 
button or press F5 to create the view.
5.Refresh the data source to display the view in the Online Browser.
Be aware that, if your SELECT query retrieves data from more than one table, and if identical column names exist in selected tables, then ambiguous column names may occur in queries such as SELECT * FROM table1, table2. Creating views from SELECT queries which contain ambiguous columns is not supported and produces an error. The text of the error is different for each database kind and may not necessarily reflect the exact cause. Therefore, before creating views from a SELECT query, make sure that it does not contain ambiguous column names. For example, if the table INVOICE has a column "id" and the table INVOICEDATA has a column "id", an error would occur for a statement such as:
CREATE VIEW `View1` AS SELECT * FROM invoice, invoicedata WHERE invoice.id = invoicedata.id; |
However, the error would be eliminated with a statement such as:
CREATE VIEW `View1` AS SELECT invoice.id AS id1, invoicedata.id AS id2 FROM invoice, invoicedata WHERE invoice.id = invoicedata.id; |
Note the syntax in code listings above applies to MySQL.
To create a view based on an existing view:
1.In the Online Browser, right-click an existing view and select Show in new SQL Editor | Create from the context menu. A CREATE VIEW statement with the definition of the existing view is displayed in a new SQL Editor window.
2.Type a new name after the CREATE VIEW part of the statement and modify the SELECT part accordingly.
6.Click the Execute button or press F5 to create the view.
3.Refresh the data source to display the view in the Online Browser.