NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-161
Examples—CREATE VIEW
•
The following 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;
•
The following 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;
•
The following 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;
•
The following 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;