Creating and using views

A view can be used to access data in one or more tables or views. You create a view by using a SELECT statement.

For example, create a view that selects only the family name and the department of all the managers:

     CREATE VIEW CORPDATA.EMP_MANAGERS FOR SYSTEM NAME EMPMANAGER AS
        SELECT LASTNAME, WORKDEPT FROM CORPDATA.EMPLOYEE
        WHERE JOB = 'MANAGER'

Since the view name, EMP_MANAGERS, is too long for a system object name, the FOR SYSTEM NAME clause can be used to provide the system name. Without adding this clause, a name like EMP_M00001 will be generated for the system object.

After you create the view, you can use it in SQL statements just like a table. You can also change the data in the base table through the view. The following SELECT statement displays the contents of EMP_MANAGERS:

     SELECT *
           FROM CORPDATA.EMP_MANAGERS

The results follow.

LASTNAME	WORKDEPT
THOMPSON	B01
KWAN	C01
GEYER	E01
STERN	D11
PULASKI	D21
HENDERSON	E11
SPENSER	E21

If the select list contains elements other than columns such as expressions, functions, constants, or special registers, and the AS clause was not used to name the columns, a column list must be specified for the view. In the following example, the columns of the view are LASTNAME and YEARSOFSERVICE.

     CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE
         (LASTNAME, YEARSOFSERVICE) AS
        SELECT LASTNAME, YEAR (CURRENT DATE - HIREDATE)
        FROM CORPDATA.EMPLOYEE

Because the results of querying this view change as the current year changes, they are not included here.

You can also define the previous view by using the AS clause in the select list to name the columns in the view. For example:

    CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE AS
        SELECT LASTNAME,
                YEARS (CURRENT_DATE - HIREDATE) AS YEARSOFSERVICE
        FROM CORPDATA.EMPLOYEE

Using the UNION keyword, you can combine two or more subselects to form a single view. For example:

CREATE VIEW D11_EMPS_PROJECTS AS
  (SELECT EMPNO
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = 'D11'
   UNION
   SELECT EMPNO
     FROM CORPDATA.EMPPROJACT
     WHERE PROJNO = 'MA2112' OR
        PROJNO = 'MA2113' OR
        PROJNO = 'AD3111')

This view has the following data.

Table 1. Results of creating a view as UNION
EMPNO
000060
000150
000160
000170
000180
000190
000200
000210
000220
000230
000240
200170
200220

Views are created with the sort sequence in effect at the time the CREATE VIEW statement is run. The sort sequence applies to all character, or UCS-2 or UTF-16 graphic comparisons in the CREATE VIEW statement subselect.

You can also create views using the WITH CHECK OPTION clause to specify the level of checking when data is inserted or updated through the view.