CREATE [ OR REPLACE ] VIEW
[ owner.]view-name [ ( column-name, ... ) ]
AS query-expression
[ WITH CHECK OPTION ]

Views do not physically exist in the database as tables. They are derived each time they are used. A view is derived as the result of a SELECT statement specified in a CREATE VIEW statement. Table names that are used in a view should specify the user ID of the table owner to distinguish tables with the same name. To create a view owned by another user you must have DBA authority and you must specify the owner.

A view name can be used in place of a table name in SELECT, DELETE, UPDATE, and INSERT statements.

SELECT * can only be used in the main query of the CREATE VIEW statement. Derived tables and subqueries must use full expressions in the SELECT list, rather than the * operator. For example, CREATE VIEW V AS SELECT * FROM T, (SELECT * FROM R) AS DT is incorrect, as the derived table DT is specified using SELECT * rather than using a SELECT list with specified expressions. Similarly, an implicit SELECT * used in a derived table is not allowed. For example, CREATE VIEW V AS SELECT * FROM T, LATERAL(proc(T.A.)) AS DT has an implicit SELECT *, as LATERAL(proc(T.A.)) is a short form for LATERAL(SELECT * FROM proc(T.A.)) and is therefore not allowed in the view definition.

Views can be updated unless the query-expression defining the view contains a GROUP BY clause, a WINDOW clause, an aggregate function, or involves a set operator (UNION, INTERSECT, EXCEPT). An update to the view causes the underlying table(s) to be updated.

The view's columns are given the names specified in the column-name list. If the column name list is not specified, view columns are given names from the SELECT list items. All items in the SELECT list must have unique names. To use names from the SELECT list items, each item must be a simple column name or have a specified alias.

SQL Anywhere does permit unnamed expressions in the SELECT list of the query-expression referenced in the CREATE VIEW statement. Unnamed expressions in the SELECT list of the query-expression are assigned the name expression, concatenated with an integer value if more than one such expression exists. For example, the following statement would define view V with three columns (expression, expression1, and expression2), and these names would appear in the SYSCOLUMN system view for the created view V.

CREATE VIEW V AS 
   SELECT DATEADD( DAY, 1, NOW() ), DATEADD( DAY, 2, NOW() ), DATEADD( DAY, 2, NOW() ) 
   FROM SYS.DUMMY;

Relying on these generated names is not recommended since other views with unnamed SELECT list expressions have the identical assigned names.

Typically, a view references tables and views (and their respective attributes) that are defined in the catalog. However, a view can also reference SQL variables. In this case, when a query that references the view is executed, the value of the SQL variable is used. Views that reference SQL variables are called parameterized views since the variables act as parameters to the execution of the view.

Parameterized views offer an alternative to embedding the body of an equivalent SELECT block in a query as a derived table in the query's FROM clause. Parameterized views can be useful for queries that are embedded in stored procedures where the SQL variables referenced in the view are input parameters for the procedure.

It is not necessary for the SQL variable to exist when the CREATE VIEW statement is executed. However, if the SQL variable is not defined when a query that refers to the view is executed, an error is returned indicating that the column (variable) could not be found.

Must have RESOURCE authority and SELECT permission on the tables in the view definition.

Automatic commit.

• CREATE TABLE statement
• CREATE MATERIALIZED VIEW statement
• ALTER MATERIALIZED VIEW statement
• INSTEAD OF triggers
• SELECT statement
• Views
• Creating a regular view (Sybase Central)
• ALTER VIEW statement

The following example creates a view showing information for male employees only. This view has the same column names as the base table:

CREATE VIEW MaleEmployees
AS SELECT *
FROM Employees
WHERE Sex = 'M';

The following example creates a view showing employees and the departments they belong to:

CREATE VIEW EmployeesAndDepartments
  AS SELECT Surname, GivenName, DepartmentName
  FROM Employees JOIN Departments
  ON Employees.DepartmentID = Departments.DepartmentID;

The following example replaces the EmployeesAndDepartments view created in the previous example. After replacing the view, the view shows the city, state, and country location for each employee:

CREATE OR REPLACE VIEW EmployeesAndDepartments
  AS SELECT Surname, GivenName, City, State, Country
  FROM Employees JOIN Departments
  ON Employees.DepartmentID = Departments.DepartmentID;

The following example creates a parameterized view based on the variables var1 and var2, which are neither attributes of the Employees nor Departments tables:

CREATE VIEW EmployeesByState
  AS SELECT Surname, GivenName, DepartmentName
  FROM Employees JOIN Departments
  ON Employees.DepartmentID = Departments.DepartmentID
  WHERE Employees.State = var1 and Employees.Status = var2;

Variables can appear in the view's SELECT statement in any context where a variable is a permitted expression. For example, the following parameterized view utilizes the parameter var1 as the pattern for a LIKE predicate:

CREATE VIEW ProductsByDescription
  AS SELECT *
  FROM Products
  WHERE Products.Description LIKE var1;

To use this view, the variable var1 must be defined before the query referencing the view is executed. For example, the following BEGIN statement could be placed in a procedure, function, or a batch statement:

BEGIN
DECLARE var1 CHAR(20);
SET var1 = '%cap%';
SELECT * FROM ProductsByDescription
END