4D Doc Center : CREATE VIEW

Home

4D v14

CREATE VIEW



	CREATE [OR REPLACE] VIEW [schema_name.]view_name [(column_list)] AS select_statement[;]

Description

The CREATE VIEW command creates an SQL view named view_name (which is a standard sql_name) containing the columns defined in the column_list parameter. You will need to specify a column name if this column is a function or is derived from an arithmetic operation (scalar). It is also necessary to specify a column name if you want to avoid having different columns with the same name (for example, during a JOIN operation) or when you want to use a different column name than the one from which it is derived.

If the column_list parameter is passed, it must contain the same number of columns as there are in the select_statement definition query of the view. If column_list is omitted, the columns of the view will have the same names as those of the columns in the select_statement of the view.

Views and tables must have unique names.

If you pass the OR REPLACE option, the view is automatically created again if it already exists. This option can be useful in order to change the definition of an existing view without having to delete/re-create/affect the privileges of objects already defined for the current view.
When the OR REPLACE option is not passed and the view already exists, an error is returned.

schema_name is also a standard sql_name and you can use it to designate the name of the schema that will contain the view. If you do not pass schema_name or if you pass the name of a schema that does not exist, the view is automatically assigned to the default schema, which is entitled "DEFAULT_SCHEMA".

select_statement designates the SELECT that is the definition query of the view. The select_statement is the same as a standard SELECT in 4D, but with the following restrictions:

You cannot use INTO, LIMIT or OFFSET clauses since the limitation, offset or assignment of variables in 4D will be performed by the MissingRef that calls the view.
You cannot use the GROUP BY clause.
Views are in read-only mode and cannot be updated.

View definition is "static" and is not updated when a source table is modified or deleted. More particularly, any columns added to a table do not appear in the view based on this table. Similarly, if you try to access deleted columns by means of a view, this causes an error.
However, a view that refers to a deleted source view will continue to work. In fact, when you create a view, it converts any view reference(s) into references to the source tables.

Views have a global scope. Once a view is created using CREATE VIEW, it can be accessed by all parts of the application (4D remote using SQL, external databases created using the CREATE DATABASE command, other databases using the SQL LOGIN command, etc.) and at any time until it is deleted using the DROP VIEW command.

Example

Here are a few examples of view definitions, given a PEOPLE table containing the following columns:

ID	INT64
FIRST_NAME	VARCHAR(30)
LAST_NAME	VARCHAR(30)
DEPARTMENT	VARCHAR(30)
SALARY	INT

A view with no restrictions:

CREATE VIEW FULLVIEW AS
        SELECT * FROM PERSONS;

A view with "horizontal" restrictions. For example, you want to only display people in the Marketing department:

CREATE VIEW HORIZONTALVIEW (ID, FirstName, LastName, Salary) AS
        SELECT ID, FIRST_NAME, LAST_NAME, SALARY FROM PERSONS
        WHERE DEPARTMENT = 'Marketing';

An aggregated view:

CREATE VIEW AGGREGATEVIEW (FirstName, LastName AnnualSalary) AS
        SELECT FirstName, LastName, SALARY*12 FROM PERSONS;

A view with "vertical" restrictions. For example, you do not want to display the SALARY column:

CREATE VIEW VERTICALVIEW (ID, FirstName, LastName, Department) AS
        SELECT ID, FIRST_NAME, LAST_NAME, DEPARTEMENT FROM PERSONS;

Once the views are defined, you can use them just like standard tables. For example, if you want to get every person whose salary is greater than 5,000 Euros:

SELECT * FROM FULLVIEW
    WHERE SALARY < 5000
    INTO :aID, :aFirstName, :aLastName, :aDepartment, :aSalary;

Another example: you want to get every person in the Marketing department whose first name is "Michael":

SELECT ID, LastName, Salary FROM HORIZONTALVIEW
    WHERE FirstName='Michael'
    INTO :aID, :aLastName, :aSalary;