CREATE VIEW statements provide a way to specify database views. A database view is a phantom table which includes data from one or more tables. Views are normally used to provide a way of flattening out complex relationships and queries so that the data appears like it is coming from a single table.
In the SELECT… FROM section of this book we discussed table aliasing. An alias is simply an abbreviation of the table that is defined in the FROM portion of a select statement and used before each column belonging to the aliased table. Aliasing tables is sometimes optional. However, when defining views it is important to always use aliases.
CREATE VIEW is defined as follows:
CREATE VIEW <view name> (view-colum1 [, view-column2, ...]) AS SELECT <column specification> FROM <table specification> [WHERE <joins and constraints>] [ORDER BY <column>]
CREATE VIEW defines a lightweight table which is defined by the select statement after the keyword AS.
The following CREATE VIEW statement, which uses the Movies database, shows how to create a view that combines the data in two tables.
CREATE VIEW TalentMovie (fullname, first, last, role) AS SELECT t.FIRST_NAME +' '+t.LAST_NAME, t.FIRST_NAME, t.LAST_NAME, r.ROLE_NAME FROM MOVIE_ROLE r, TALENT t WHERE t.TALENT_ID = r.TALENT_ID
TalentMovie is the name of the new view. The view name must be different than those of previously defined tables and views.
(fullname, first, last, role) specifies the view columns. Each of these maps to the columns specified in the select statement. The fullname column is derived by concatenating the FIRST_NAME and LAST_NAME columns in the TALENT table.
Views make it easy to flatten complex sets of information into a table-like form. While you are not restricted as to how the tables underneath the views may be updated and changed, with views you can only update existing records. Update statements referring to a view must only update columns that map to columns from a single table. In the above example, for instance, you can not update FIRST_NAME and ROLE_NAME in the same UPDATE statement.
Selecting information in a view is similar to selecting information in any other table. Here is an example of how to select the information from the view created above:
SELECT fullname, first, last, role FROM TalentMovie ORDER BY last
This select statement will return all the talent names and their roles. It is important to understand that the TalentMovie defines a window into the TALENT and MOVIE_ROLE tables of the MOVIE database. All modifications to data in these tables will be reflected when selecting data from the view.
Once you no longer need the view you can drop it by using:
To drop this view you would simply use:
DROP VIEW TalentMovie
You can display all the views that you have a created on a database by using:
SELECT DISTINCT tablename FROM _SYS_TABLES WHERE category='VIEW'
Is is important to remember that the OpenBase VIEW mechanism is virtual. What this means is that a query into a view is effectively rewritten to query the viewed table(s) directly, rather than making a table representing the viewed data and querying into that.
So, when you issue a SELECT * query on a VIEW, will mean that the query will perform a select all on the viewed table(s).
Select * is not generally an effective way of returning all data from a table. Without specify the specific columns, and thus column order, the column order that is returned may not be what you were expecting. This relates to Natural Column Order and is the reason why you should specify the column order when you perform a select, as previously discussed.