Creating and using a view

 

You might find that no single table in the database contains all the information that you need. You might also want to give users access to only part of the data in a table. Views provide a way to divide the table so that you deal with only the data that you need.

A view reduces complexity and, at the same time, restricts access. To create a view, have the correct authority to the tables or physical files on which the view is based. See the CREATE VIEW statement in the SQL reference topic collection for a list of authorities needed.

If you did not specify column names in the view definition, the column names are the same as those for the table on which the view is based.

You can make changes to a table through a view even if the view has a different number of columns or rows than the table. For INSERT, columns in the table that are not in the view must have a default value.

You can use the view as though it were a table, even though the view is totally dependent on one or more tables for data. The view has no data of its own and therefore requires no storage for the data. Because a view is derived from a table that exists in storage, when you update the view data, you are really updating data in the table. Therefore, views are automatically kept up-to-date as the tables they depend on are updated.

• Creating a view over a single table
  This example shows how to create a view over a single table. The view is built on the INVENTORY_LIST table. The table has six columns, but the view uses only three of them: ITEM_NUMBER, LAST_ORDER_DATE, and QUANTITY_ON_HAND.

• Creating a WHERE clause
  With a WHERE clause on a table or a view, you can display only records that match certain criteria.

• Creating a view that combines data from multiple tables
  If you want to display data from multiple tables together, create a single view that combines data from these tables.

 

Parent topic:

Getting started with iSeries Navigator