Views That Use Views | Advantage Database Server: The Official Guide

Earlier in this chapter, you learned how to execute a SQL SELECT statement against the Employee Tab view using the Native SQL Utility. Since a view itself is defined using a SQL statement, there is no reason why the SQL SELECT statement used in a view cannot query a view.

Views that use views can be employed in a number of interesting ways. Two of these are covered in this section. In the first technique, you will learn how views can be used to modularize operations on your data. In the second, you will see how a view can be used as a temporary table.

Modularizing Operations with Views

One reason for creating a view that selects from a view is that it permits you to break up data operations into manageable modules. For example, you may have a view that performs a calculation for each record in a table. You can then create another view that performs an aggregation, such as a SUM operation, on the calculation performed by the first view. This view, in turn, can be used in a third query to perform additional manipulation.

One of the advantages to this approach is that some views may get used over and over by other views.

The following steps demonstrate how to create views that are used by other views:

If the DemoDictionary data dictionary is not open in the Advantage Database Manager, open it.
Right-click the VIEWS node in the Advantage Database Manager’s tree view and select Create.
Set Name to Sum by Invoice.

Enter the following SQL statement in the SQL section:

SELECT SUM(Quantity * Price) as "Inv Total", "Invoice No"   FROM ITEMS GROUP BY "Invoice No"

Click Create to save this new view. You should see a node for this Sum by Invoice view under the VIEWS node in the Advantage Database Manager tree view.
Right-click VIEWS again and select Create.
Set Name to Sales by Employee.

Enter the following SQL statement in the SQL section:

SELECT SUM(SInv."Inv Total") as "Total Sales", Inv."Employee ID",   Emp."First Name", Emp."Last Name" FROM "Sum by Invoice" SInv,   INVOICE Inv, EMPLOYEE Emp   WHERE SInv."Invoice No" = Inv."Invoice No" and   Inv."Employee ID" = Emp."Employee Number"   GROUP BY Inv."Employee ID", Emp."Last Name", Emp."First Name"

Click Create to save this view. You should see a node for this Sales by Employee view under the VIEWS node in the Advantage Database Manager tree view.
Right-click Views once more and select Create.
Set Name to Purchases by Customer.

Enter the following statement in the SQL section:

SELECT SUM(SInv."Inv Total") as "Total Purchases", Inv."Customer ID",   Cust."First Name", Cust."Last Name" FROM "Sum by Invoice" SInv,   INVOICE Inv, CUSTOMER Cust   WHERE SInv."Invoice No" = Inv."Invoice No" and   Inv."Customer ID" = Cust."Customer ID"   GROUP BY Inv."Customer ID", Cust."Last Name", Cust."First Name"

Click Create to save the view. You should see a node for this Purchases by Customer view under the VIEWS node in the Advantage Database Manager tree view.
Select the Sales by Employee view node under the VIEWS node. Click the Open button to execute the view. The results, shown in Figure 6-6, are displayed in the Table Browser. Close the Table Browser when you are done inspecting the data.

Figure 6-6: The Sales by Employee view in the Table Browser
Now select the Purchases by Customer view node. Click Open to display this data in the Table Browser. Close the Table Browser when you are done.
Take a moment now to grant read access rights to the three views that you just created to both the ALL and the READONLY groups. (The steps you use to enable view rights were discussed earlier in this chapter in the section “Making Views Accessible.”) It is not necessary to enable any further rights, since the use of aggregate operations and joins in these views renders them readonly.

This example demonstrates the modular aspect of views. Both the Sales by Employee and the Purchases by Customer views select data from the Sum by Invoice view. If one or more future views also need to work with the summary data in the Sum by Invoice view, they can easily select from it.

Note

Views that query views may execute more slowly than a single complicated view. If performance is important for your application, you may want to compare the performance of your views with alternative data access mechanisms, such as SQL queries and scopes (ranges).

Views as Temporary Tables

Some operations cannot be performed using a single query. For example, you may need to execute one query to create a result set with intermediate results. You then use the first query’s result from another query. Situations like these often call for the creation of a temporary table that will be used to store the intermediate results, so that the second query can be executed against these records.

Creating temporary tables in a client application requires careful planning, considering the multiuser environment in which most applications are running. At a minimum, the use of a temporary table involves the following steps:

The creation of a temporary table into which records can be inserted.
Devising a mechanism to guarantee that this temporary table name is unique on a connection-by-connection basis. This is necessary in order to isolate the operations initiated by one client application from any others that happen to be running.
Deleting the temporary table when it is no longer needed.

Failure to correctly implement these steps can result in one of the most difficult problems to debug: an operation that occasionally fails for no apparent reason.

Instead of using a temporary table, which requires you to manage the lifecycle of that table, you can use a view instead. When you use a view as a temporary table, ADS is responsible for creating, naming, and destroying the temporary table. Fortunately, ADS does this reliably, with no danger of conflict between client connections.

Consider the Sum by Invoice view created in the preceding section. This view returns the type of data that you could have placed into a temporary table. Then, a query similar to the one you entered into the Sales by Employee view could have been used to process the records in this temporary table. Thankfully, the use of views made all of that complexity unnecessary.