CREATE VIEW

Use the CREATE VIEW statement to create a view, by selecting certain columns and/or rows from one or more tables or other views.

Usage

CREATE [PURE] VIEW view[({view-column-name},...)]
AS <selection> [WITH CHECK OPTION]

Notes

Creating a view only creates a definition—it doesn’t create an image. (Use the CREATE VIEW IMAGE command to create an image of a view).

If you don’t include a view-column-name specification, the columns in the new view inherit names from the columns returned by the selection. You must supply a view-column-name if any column that the selection returns has the same name as any other column, or if it is a combination or aggregation of other columns.

If you specify the WITH CHECK OPTION, the RDBMS checks each INSERT and UPDATE operation to ensure that the resulting rows meet the search criteria in the original view definition. Kognitio SQL Guide, March 2016
Kognitio recommend that you use the WITH CHECK OPTION whenever you create an updateable view.

Non-SYS users cannot create views that are based on any views in the SYS schema that begin with the string IPE_, as these need to be updated from time to time.

It is possible to specify that a view cannot have an image using the PURE keyword. This is important for the system table views, such as IPE_TABLE, as creating an image would prevent them showing tables created after the image was created.

This can also be useful for user views which should not have an associated image; either because the image will require too much memory, or because it is important to always see the latest data in the image.

Example 1: Create a Simple View from a Single Base Table

Create a view UK_CUSTOMERS, derived from the single base table, CUSTOMER:

    CREATE VIEW uk_customers(
        custno, name, address, postcode, phone, acctbal) AS
    SELECT c_custkey,
    c_name,
    c_address,
    c_postcode,
        c_phone,
    c_acctbal
    FROM customer
WHERE c_nationkey = 1

You can select all rows from the view, even though there is no view image—view images are not created automatically when you create a view.

Note you can update this view, as it is based on a single table and there are no calculated columns. So, the following INSERT works:

INSERT INTO uk_customers VALUES(
    10, 'Marys Mercs', 'Smart St, Smalltown',
    'ST1 4PZ', 1, '44(0)1234571199', 0.00)

Example 2: A View with a Redefined Column

Create a view CUST_SHORT_PCODE to use for survey analysis. Here, the postcode column is redefined so that only the first 4 digits (the outgoing postcode) are retrieved, and the data type becomes CHAR(4):

CREATE VIEW cust_short_pcode(custno, name, address, p_code) AS
SELECT c_custkey,
c_name,
c_address,
    CAST(SUBSTRING(c_postcode FROM 1 FOR 4) AS CHAR(4))
FROM customer

Note you cannot update this view, because the p_code column is calculated.

Example 3: View Based on Multiple Tables

The SUPPLIER table has a column n_nationkey, which is an INTEGER. It may be convenient to set up a view that shows the nation name from a NATION lookup table, rather than a number, and also to re-label the columns:

CREATE VIEW supp_nation(suppno, name, address, nation, phone, url,
    telnet, balance, comment) AS
SELECT s_suppkey,
s_name,
s_address,
n_name,
s_phone,
    s_url,
s_telnet,
s_balance,
s_comment
FROM supplier, nation
WHERE s_nationkey = n_nationkey

Note that you cannot update this view, because it derives from more than one table.

To satisfy this query the join must be performed. So, if the view is to be referenced in multiple queries, it may be beneficial to create the view image.

Example 4: A View Based on Another View

Here we create a view based on the existing view, UK_CUSTOMERS, rather than selecting from the CUSTOMER table:

    CREATE VIEW smalltown_suburbs AS
    SELECT *
FROM uk_customers
    WHERE LOWER(address) LIKE '%smalltown%' AND
        UPPER(postcode) NOT LIKE '%ST1 %'