SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-171
Examples—CREATE VIEW
Examples—CREATE VIEW

This example creates a shorthand view that includes part numbers and supplier
numbers for parts in which fewer than ten are in stock:
CREATE VIEW GETPARTS (PNUM, SNUM)
AS SELECT P.PARTNUM, SUPPNUM FROM PARTLOC P, PARTSUPP S
WHERE P.PARTNUM = S.PARTNUM AND QTY_ON_HAND < 10
CATALOG $VOL1.INVENT;

This example creates a protection view on the table EMPLOYEE that contains
rows with employee numbers greater than 1000. The view is secured so that
anyone on the network can read the view, but only a local user with super ID
authority can write to it. Any member of the owner's user group can purge the view.
CREATE VIEW \SYS1.$VOL1.PERSNL.EMPVIEW
AS SELECT *
FROM \SYS1.$VOL1.PERSNL.EMPLOYEE WHERE EMPNUM > 1000
FOR PROTECTION
CATALOG \SYS1.$VOL1.PERSNL
SECURE "N-NC"
WITH CHECK OPTION;

This shorthand view retrieves average salary for each department:
CREATE VIEW \SYS1.$VOL1.PERSNL.DAVGSAL (DNUM, AVSAL)
AS SELECT DEPTNUM, AVG(SALARY)
FROM EMPLOYEE GROUP BY DEPTNUM CATALOG PERSNL;

This view retrieves the annual salary for each employee and assigns the heading
“ANNUAL SALARY” to the ANNUAL_SALARY column. All other columns inherit
headings from the underlying table, EMP.
CREATE VIEW EMPV
(EMPNUM, EMPNAME,
ANNUAL_SALARY HEADING "ANNUAL SALARY", DEPTNUM)
AS SELECT EMPNUM, EMPNAME, SALARY * 12, DEPTNUM
FROM EMP WITH HEADINGS;

This example shows an inappropriate way to join CUSTOMER and ORDERS. The
CUSTOMER table has 100 rows, and the ORDERS table has 300 rows. Because
the SELECT statement that defines the view does not include a WHERE clause,
each row in the CUSTOMER table is concatenated with each row in the ORDERS
table, resulting in a view with 30,000 rows.
Each row has the number of columns indicated in the column list of the CREATE
VIEW statement. Most rows in the view have no meaning because they are a
concatenation of a customer with an unrelated order. (See the next example for a
better way to join these two tables.)
CREATE VIEW BAD
(C_CUSTNUM,O_CUSTNUM,CUSTNAME,STATE,ORDERNUM)
AS SELECT C.CUSTNUM,O.CUSTNUM,CUSTNAME,STATE,ORDERNUM
FROM SALES.CUSTOMER C, SALES.ORDERS O CATALOG SALES;