Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Creating Views


A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.

Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).

Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.

View names can be up to 128 characters long. Views are defined in terms of a SELECT statement; the result of the SELECT statement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.

Creating a View

Create a restriction view on the CUSTOMERS table called CUSTOMER_DETAILS containing limited information:
 CREATE VIEW customer_details (surname, forename, address_1,
                               address_2, town, postcode, title,
                               date_of_birth, country_code,
                               customer_id)
    AS SELECT surname, forename, address_1, address_2, town, postcode,
              title, date_of_birth, country_code, customer_id
    FROM customers;

If the view definition does not include a list of column names, the columns in the view will be named after the columns listed in the SELECT clause.

Create a join view, including an outer join:
 CREATE VIEW product_details
    AS SELECT product, COALESCE(producer, ' ') AS producer, format, 
              price, stock, reorder_level, release_date, ean_code, 
              status, product_search, item_id, category_id, product_id, 
              display_order, image_id
    FROM products
    NATURAL JOIN items
    NATURAL JOIN formats
    NATURAL LEFT OUTER JOIN producers;

Check Options

Check options can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.

 CREATE VIEW swedish_customers
    AS SELECT *
    FROM customer_details
    WHERE country_code = 'SE'
    WITH CHECK OPTION;
 

The check option in the view definition (WITH CHECK OPTION) means that no new rows may be inserted into the view if the value for the COUNTRY_CODE column is not SE.

If there is an instead of trigger defined for the view, the WITH CHECK OPTION does not have any effect.

Creating Views Based on Other Views

Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.

 CREATE VIEW customer_addresses (
       surname,
       forename,
       recipient,
       address_1,
       address_2,
       town,
       postcode,
       country,
       salutation,
       customer_id)
    AS SELECT
       surname,
       forename,
       UPPER(recipient(title, forename, surname)),
       address_1,
       COALESCE(address_2, ' '),
       UPPER(town),
       UPPER(postcode),
       UPPER(country),
       salutation(title, forename, surname, date_of_birth, country_code),
       customer_id
    FROM customer_details
    JOIN countries ON code = country_code;


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX