SQL — Views

Syntax

CREATE VIEW viewed-table-name
[(column-identifier [, column-identifier]…)]
AS
SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition]

Description

A relational view is a mechanism to create a virtual table that has built-in projection, join, and/or restrictions that do not physically exist on disk. For example, a view may look at the customer table, but only display the customer last name and the customer first name fields. Alternatively, a view may look at the customer table but only display those rows that correspond to customers who are in a particular region. For most practical purposes, a view can be treated as a table. Additionally, a view may represent the customer table joined with the order table, or any combination of these may occur in a view.

After a view is created, it can be treated as a base table in any future queries and may even be the target of an insert, update, or delete, given enough view information. It is not possible at this time to create a view of a union or a view with a GROUP BY with the BASIS DBMS using and PRO/5 data dictionary.

A view has three primary components: columns, tables, and a WHERE clause. Each of these components is represented in a BASIS Data Dictionary physical file.

Views are advantageous because they allow the arrangement of non-normalized data and show specific parts of the data without changing the code. If there is a table with dozens of columns and only five of those columns need to be accessed, a view can easily be created that only presents these five columns to the end-user. Views will show only specific parts of the data selected by the user.

The demand for views involves non-normalized data. This is the intermixing of record types into a single file and has been a common practice for legacy BBx Applications. These applications require a mechanism to view one physical file as more than one logical file without actually creating two new files.