Nonstop Database, HiRDB Version 9 UAP Development Guide
Defining a view table derived from other tables to view specific columns and rows allows you to restrict the table data that can be manipulated.
This section describes definition and manipulation of view tables, using as examples the stock table and sales table shown in the following figure.
Figure 2-40 Tables used in examples of manipulating view tables
Five examples of defining view tables are provided in this section:
The following figure shows an example of defining a view table to limit the columns to be searched. In this example, view table V1 is derived from the stock table so that the columns that can be searched do not include the color column.
Figure 2-41 Example of defining a view table for limiting the columns to be searched
The figure below shows an example of using search conditions to define a view table. In this example, a query is used to create view table V2 from the stock and sales tables in order to determine which products at each branch have sold fewer than 10 items.
Figure 2-42 Example of using search conditions to define a view table
The figure below shows an example of defining a read-only view table. In this example, a query is used to create read-only view table V3 from the stock table to determine those products whose price is higher than the average price of all products; the retrieved information includes the product codes, product names, prices, and stock quantities.
Figure 2-43 Example of defining a read-only view table
The figure below shows an example of defining a view table from which duplications are eliminated. In this example, view table V4 is created from the stock table; in view table V4, duplicated product names and prices are eliminated.
Figure 2-44 Example of defining a view table from which duplications are eliminated
The figure below shows an example of defining a view table from another view table. In this example, a query is used to define view table V5, which is to consist of the rows in view table V1, defined in (a), that contain SKIRT as the product name.
Figure 2-45 Example of defining a view table from another view table
The figure below shows an example of manipulating a view table. In this example, the product name, branch, and sales total of the product with the highest sales total are retrieved from the view table (V2) defined in (1)(b) Using search conditions to define a view table. (The view table is specified in the SQL statement that specifies the subquery.)
Figure 2-46 Example of manipulating a view table
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.