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 AS
        SELECT LASTNAME, WORKDEPT FROM  CORPDATA.EMPLOYEE         WHERE JOB = 'MANAGER'

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.

 

Parent topic:

Data definition language

 

Related concepts


Retrieving data using the SELECT statement
Sort sequences and normalization in SQL

 

Related reference


Using the UNION keyword to combine subselects
CREATE VIEW