Creating a view on a single table

 

You can create a view on a single table to show a subset of the data that the table contains. Compared with the original table, the view can have fewer records and fewer columns, and the columns in the view can have a different order. The following example procedure shows how to create a view on a single table. The view is built on the INVENTORY_LIST table. The table has six columns, but the view uses only three of the columns: ITEM_NUMBER, LAST_ORDER_DATE, and QUANTITY_ON_HAND. The order of the columns in the SELECT clause is the order in which they appear in the view. The view contains only the rows for items that were ordered in the last two weeks. The CREATE VIEW statement looks like this:

1. Use the following command to create the view:

   CREATE VIEW SAMPLECOLL.RECENT_ORDERS AS 		SELECT ITEM_NUMBER, LAST_ORDER_DATE, QUANTITY_ON_HAND 				FROM SAMPLECOLL.INVENTORY_LIST 				WHERE LAST_ORDER_DATE > CURRENT DATE - 14 DAYS

   In the preceding example, the columns in the view have the same name as the columns in the table because no column list follows the view name. The schema that the view is created into does not need to be the same schema as the table it is built over. Any schema or library can be used.

2. Run this statement:

   SELECT *FROM SAMPLECOLL.RECENT_ORDERS

The result looks like this.

                                 Display Data                                              Data width . . . . . . :      26
Position to line  . . . . .              Shift to column  . . . . . .
....+....1....+....2....+.
ITEM    LAST      QUANTITY NUMBER  ORDER     ON         DATE      HAND 303476  05/30/94      100
********  End of data  ********
                                                                       Bottom F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

The only row selected by the view is the row that you updated to have the current date. All other dates in the table still have the null value so they are not returned.

 

Parent topic:

Creating and using a view