SQL Statements › CREATE VIEW › Processing › Example 3

Example 3

Create an updateable view with the specifications listed in the following:

1. View name: SUPPLY
2. Column names: SNUM, SNAME, STATUS, CITY, STATE, ZIP
3. Search condition: The value of STATUS must be greater than 10
4. Use the WITH CHECK OPTION

This view is a row subset view of the table CASUPL which contains columns SNUM, SNAME, STATUS, CITY, STATE and ZIP. View SUPPLY can be used to update values for all the columns, and to insert new rows into CASUPL where the value for STATUS is greater than 10. If you attempt to insert a row where the value of STATUS is less than or equal to 10, the row is not inserted because the WITH CHECK OPTION does not allow you to view any row where STATUS is not greater than 10.

EXEC SQL
      CREATE VIEW SUPPLY
           (SNUM, SNAME, STATUS, CITY, STATE, ZIP)
      AS SELECT ALL
           SNUM, SNAME, STATUS, CITY, STATE, ZIP
      FROM CASUPL
      WHERE STATUS > 10
      WITH CHECK OPTION
END-EXEC