SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-38
Rules for Creating and Accessing Views
subqueries— from several SQL/MX tables. You cannot use columns from SQL/MP
tables.
Use the CREATE VIEW statement to define new column names for a view, instead of
using the column names from the underlying tables. When using the view, applications
use the view-defined column names. Applications can also use views to rename,
reorder, and project subsets of columns from one or more tables.
For information about the CREATE VIEW statement, see the SQL/MX Reference
Manual. For background information about views, see Section 4, Understanding and
Planning SQL/MX Tables.
Before you create views for your SQL/MX database, see the SQL/MX Query Guide for
information about formulating queries.
Rules for Creating and Accessing Views
•
A view can have a single underlying table or multiple tables.
•
To create a view, you must have SELECT privileges for the underlying table.
•
Granting a privilege on a view does not grant that privilege to the corresponding
column of the underlying table.
For more information, see Access Privileges for SQL/MX Database Objects on
page 7-9.
Examples for Creating Views for SQL/MX Tables
This example creates a view on a single table without a view column list:
CREATE VIEW SALES.MYVIEW1 AS
SELECT ordernum, qty_ordered FROM SALES.ODETAIL;
This example creates a view with a column list:
CREATE VIEW SALES.MYVIEW2
(v_ordernum, t_partnum) as
SELECT v.ordernum, t.partnum
FROM SALES.MYVIEW1 v, SALES.ODETAIL t;
This example creates a view with a CHECK OPTION:
CREATE VIEW SALES.MYVIEW3
(ordernum HEADING 'Number of Order') AS
SELECT ordernum FROM SALES.ODETAIL
WHERE partnum < 1000 WITH CHECK OPTION;
This example creates a view from two tables by using an INNER JOIN:
CREATE VIEW MYVIEW4
(v_ordernum, v_partnum) AS
SELECT od.ordernum, p.partnum
FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P
ON od.partnum = p.partnum;