CREATE VIEW

CREATE VIEW

Syntax

create_view ::=

query_restriction_clause ::=

Prerequisites

At least one of the following conditions must be met:

• The SYS user
• Users have the CREATE VIEW or CREATE ANY VIEW system privilege to create a view in their own schema
• Users have the CREATE ANY VIEW system privilege to create a view in another user's schema

Description

This statement is used to create a view having the specified name. A view is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

OR REPLACE

Use the OR REPLACE clause to replace a view having the same name if such a view already exists. This clause is used to change the definition of an existing view without having to drop the old view, create the new view, and grant previously granted privileges for the view.

FORCE

The FORCE clause is used to specify that the view is to be created even if the objects on which the view is based don't exist, and even if the owner of the schema containing the view does not have sufficient privileges to access the view.

This means that it is possible to use the FORCE option to create views that are semantically erroneous and thus invalid. In such cases, because an error will occur when a SELECT statement is executed on the view, it is advisable to test the view after creating it by executing a SELECT statement on it immediately, or to query the SYS_VIEWS_ meta table to verify that no errors were raised, which can be inferred to mean that the view is error-free.

NO FORCE

Use the NO FORCE clause to specify that the view is to be created only if the underlying objects exist and the owner of the schema in which the view is to be created has access privileges for them. This is the default view creation behavior.

user_name

This is used to specify the name of the owner of the schema in which the view is to be created. If this value is omitted, Altibase will create the view in the schema of the user who is connected via the current session.

view_name

This is used to specify the name of the view to create. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names.

alias_name

If the query on which a view is based contains an expression without an alias, an alias for the expression must be specified. This alias will becomes the name of the corresponding expression in the view. The number of aliases must be same as the number of expressions and columns in the query.

subquery

This is used to specify the text of a query that identifies rows and columns in the base table(s) to display in the view.

WITH READ ONLY

This is used to specify that the view will be a read-only view. If you do not specify this option, the updatable view is created that can perform INSERT, UPDATE, and DELETE operations on views.

Limitations on the Use of Queries in Views

• The owner of the schema where the view is stored must have the necessary privileges to execute the SELECT statement from the table or view on which the view is based.
• A maximum of 1024 expressions can be specified in the SELECT statement on which a view is based.
• The CURRVAL and NEXTVAL pseudocolumns cannot be used in the SELECT statement on which a view is based.

Examples

Creating a view

<Query> In the following example, a view called avg_sal will be created on the basis of the employees table. The purpose of the view is to display the average salary for each department.

iSQL> CREATE VIEW avg_sal AS
  SELECT dno, AVG(salary) emp_avg_sal
FROM employees
  GROUP BY dno;
Create success.
iSQL> SELECT * FROM avg_sal;
AVG_SAL.DNO  AVG_SAL.EMP_AVG_SAL 
------------------------------------
A001  2066.66667  
C001  1576.66667  
C002  1660     
D001  2075.75   
F001  1845     

6 rows selected.

Since emp_avg_sal is provided as an alias for the expression AVG (salary) within the subquery, the alias for the column of the view does not need to be specified.

Creating a Join View¹

<Query> The following view shows the names of the employees responsible for the ordered products and the names of the customers who ordered the products.

iSQL> CREATE VIEW emp_cus AS 
 SELECT DISTINCT e.e_firstname, e.e_lastname, 
  c.c_firstname, c.c_lastname 
 FROM employees e, customers c, orders o 
 WHERE e.eno = o.eno AND o.cno = c.cno;
Create success.
iSQL> SELECT * FROM emp_cus;
E_FIRSTNAME           E_LASTNAME            C_FIRSTNAME           C_LASTNAME
---------------------------------------------------------------------------------------------
Alvar                 Marquez               Estevan               Sanchez
Sandra                Hammond               Pierre                Martin
.
.
.
William               Blake                 Saeed                 Pahlavi
Sandra                Hammond               Saeed                 Pahlavi
22 rows selected.

---

1. A join view is a view in which the underlying query contains a join. ↩