Use the CREATE VIEW command to define a view that combines columns from existing tables or views.
Options
AS SELECT clause
Specifies the columns and rows to include in the view. As a rule anything that is acceptable in an ordinary select clause will work here. If you are planning on using expressions you should considering using the SELECT AS notation to give each column meaningful names.
(collist)
Specifies a list of one or more column names or aliases, separated by a comma (or the current delimiter). These names will be the column headings displayed in the result of a SELECT command or the Data Browser.
TEMPORARY
Creates a temporary view that disappears when the database is disconnected.
viewname
Specifies a view name.
WITH CHECK OPTION
Specifies that a row cannot be added or updated unless it meets the conditions included in the WHERE clause (which is part of the SELECT clause). R:BASE uses this option only on a view that can be updated.
About the CREATE VIEW Command
CREATE VIEW defines a view to store in the sys_views table. You can use a stored view whenever necessary. Unlike a table, stored views contain no data. R:BASE collects data for the view from the source tables or views when a command calling the view is run.
A view is the most efficient way to gather data from separate tables or views into one location. A view that can be updated allows you to enter, change, and delete data from the source table. The number of tables in a view is dependent on available memory.
You can define a view containing a maximum of 400 columns from as many tables or views as memory allows. However, a view is still limited to the 32,786 character row-size limit. You can use the SQL symbol asterisk (*) to include all columns from all tables or views, or you can specify the columns you want to include. You can combine these two methods to include all columns from one table or view and specify columns from another table or view. You must separate column, table, and view names with commas (or the current delimiter character).
Avoiding Multiple Occurrences of Columns
When you use only an asterisk (*) in the SELECT clause, the view will contain all columns from all tables or views. If the tables or views contain common columns, the view will contain multiple occurrences of those columns.
To avoid multiple occurrences of common columns, specify which columns to include in the view. For example, to include all columns from one table but only certain columns from another table, use an * for the first table, then list the column names to be included from the second table. You can specify the columns for a view as t1.*, t2.col2, t2.col3, where t1.* specifies all columns from table t1and t2.col2, t2.col3 specifies two columns from table t2. Be sure that the list does not include the common columns contained in the second table. When you use a combination of * and column names, you must specify the table with which * is associated. However, you can omit the table or correlation name for the columns listed individually if those columns occur in only one table in the view.
Duplicate Rows
If the tables forming a view contain duplicate rows, either individually or in combination with other tables in the view, multiple duplicate rows will be displayed. Usually, the presence of duplicate rows in a view indicates a database design problem. Check your database structure for design flaws such as redundant data storage.
Linking Columns
When you build a view from two or more tables or views, define the relationship between the source tables and views by identifying linking columns in a WHERE clause. Linking columns are columns that contain the same values; their names can be the same or different. For example, the following WHERE clause specifies that a view displays only those rows where the values in t1.col1 are equal to the values in the common column t2.col1.
WHERE t1.col1 = t2.col1
UNION Operator
Use views to create a subset of information. The conditions that link the source tables or views in the WHERE clause will limit the rows used in the view. However, you can create a view that uses all the rows from two or more tables by using the UNION operator. The UNION operator allows you to specify different selection criteria for each table in the view, as shown in the Examples below.
Updating Views
You can update the data for columns in a view when the view does not contain a UNION operator, and its SELECT clause meets the following requirements:
•The clause does not specify DISTINCT.
•The clause does not include a sub-SELECT command in the WHERE clause.
•The clause does not include a GROUP BY or HAVING clause.
•The clause does not include an ORDER BY clause.
When you add, change, or delete rows by updating a single table view, you also modify the data in the source table. In multi-table views you cannot add, edit, or delete rows. Any additions or changes to data made through a view are subject to all the user-defined rules specified for the table when it was constructed. In addition, if you specify the WITH CHECK OPTION for the view, you can only add or modify rows that meet the conditions defined in the WHERE clause.
You can only use the DELETE, EDIT, INSERT, LOAD, and UPDATE commands with views that can be updated. If a view cannot be updated, you can use the view only to display data or as the basis for reports.
Other Commands to be Used with CREATE VIEW
Use the following commands with any view created with CREATE VIEW.
* These commands will display data when using a multi-table View (a View based on multiple tables), but the data will not be editable. A View with a GROUP BY parameter is also not editable.
** The UPDATE command will not update data when using a multi-table View (a View based on multiple tables), as the data is not editable. A View with a GROUP BY parameter is also not editable.
Views Compared with Look-up Tables
If the data used in a report is stored in more than one table, using a view is more efficient than a driving table and several look-up tables because it takes less time to print the report using a view. Using a view is more efficient because R:BASE gathers the data for a view before, rather than during, printing.
Database Access Rights with CREATE VIEW
The access rights that can be assigned with the GRANT command depend on whether or not the view can be updated. The ALL PRIVILEGES, DELETE, INSERT, SELECT, and UPDATE access rights can be granted on a view that can be updated. You must have ALL PRIVILEGES or SELECT access rights on a table or view to include it in a view.
If you have been assigned the SELECT access right and the WITH GRANT OPTION has been assigned on all the source tables or views used in a view, you can grant both SELECT and the WITH GRANT OPTION to other users.
If you are the database owner or you have the WITH GRANT OPTION on a view, you can also assign access rights on stored views.
Changing Views
You cannot change a view at the R> Prompt. To change a view from the R> Prompt, you must delete the view by using the DROP command, then define a new view. However, you can use the Query Builder to change a view.
Examples
The following command defines a view that can be updated and specifies a subset of columns "custid, company, custaddress, custcity, custstate, and custzip" from one table, customer. The column list must match the number of columns in the SELECT clause; the names, however, can be different. The WHERE clause restricts the rows to those with zip codes ranging from 40001 through 49999. The WITH CHECK OPTION specifies that only rows that meet the condition included in the WHERE clause can be added or changed in the database.
CREATE VIEW cust_addr (custid, custcompany, custaddress, custcity, custstate, +
custzip) AS SELECT custid, company, custaddress, +
custcity, custstate, custzip FROM customer WHERE custzip +
BETWEEN 40001 AND 49999 WITH CHECK OPTION
The following command defines a view that will display only those rows from the customer and transmaster tables that have matching values in the common column custid. Therefore, only the rows that have customers who have had a transaction will be included in the view. The command line ORDER BY custid tells R:BASE to sort the rows and display them by the customer identification number.
CREATE VIEW cust_trans AS SELECT t1.custid, company, netamount +
FROM customer t1, transmaster t2 WHERE t1.custid = t2.custid +
ORDER BY custid
The following command defines a view that will display only those rows from the customer table where the values in the custidcolumn do not exist in the transmaster table. Therefore, only the rows that have customers who have not had a transaction will be included in the view.
CREATE VIEW cust_notrans AS SELECT custid, company FROM +
customer WHERE custid NOT IN (SELECT custid FROM transmaster) +
ORDER BY custid
The following command combines the commands in the two preceding examples, creating a view that will display all rows from both the customer and the transmaster tables. The UNION operator joins the two SELECT clauses, allowing you to display rows for all customers whether or not they have had a transaction.
The first SELECT clause instructs R:BASE to include the rows from both tables where the values in custid match. The second SELECT clause instructs R:BASE to include rows from the customer table where there are no values for custid in the transmaster table.
When you use the UNION operator, the number of columns specified in the SELECT clauses must be the same and the data types of the columns must be compatible. If there is no column in one table that matches a column listed in the other table's SELECT clause, you must substitute a value (or null value). Because the netamount column does not exist in the customer table used in the second SELECT statement, the value $0.00 was entered in place of netamount.
CREATE VIEW all_cust_trans AS SELECT t1.custid, company, netamount +
FROM customer t1, transmaster t2 WHERE t1.custid = t2.custid +
UNION SELECT custid, company, $0.00 FROM customer +
WHERE custid NOT IN (SELECT custid FROM transmaster) +
ORDER BY custid
