SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-108
Examples of CREATE VIEW
•
It cannot contain a GROUP BY or HAVING clause.
•
It cannot directly contain the keyword DISTINCT.
•
The FROM clause must refer to exactly one table or one updatable view.
•
It cannot contain a WHERE clause that contains a subquery.
•
The select list cannot include expressions or functions or duplicate column names.
An updatable view is insertable if the column list does not include a SYSKEY from the
underlying base table.
Examples of CREATE VIEW
•
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 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;