8.6. Nesting Views

An important feature of views is that they can be based on other views. This is called nesting. One view can access data that comes through another view. In fact there isn’t a limit to the number of view layers that can be defined. Because of this, views can be a very convenient and flexible way for programmers to retrieve information. Although the number of views that can be nested is virtually unlimited, programmers should use care to avoid nesting views too deeply. Performance- and maintenance-related issues can result, especially if the views are built many layers deep.

To see how views can be based on other views, two views will be created — one referencing the PRODUCTS table and the other referencing the INVOICE table. In the first example, WORKSTATION_PRODUCTS_VIEW includes only products related to workstations and excludes the manufacturer number. When accessed from the SAS Windowing environment, the view produces the results displayed below.

SQL Code

PROC SQL;
  CREATE VIEW WORKSTATION_PRODUCTS_VIEW AS
    SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST
      FROM PRODUCTS
        WHERE UPCASE(PRODTYPE)="WORKSTATION";
QUIT;

Results

In the next example, INVOICE_1K_VIEW includes rows where the invoice price is $1,000.00 or greater and excludes the manufacturer number. When accessed from the SAS Windowing environment, the view renders the results displayed below.

SQL Code

PROC SQL;
  CREATE VIEW INVOICE_1K_VIEW AS
    SELECT INVNUM, CUSTNUM, PRODNUM, INVQTY, INVPRICE
      FROM INVOICE
        WHERE INVPRICE >= 1000.00;
QUIT;

Results

The next example illustrates creating a view from the join of the WORKSTATION_PRODUCTS_VIEW and INVOICE_1K_VIEW views. The resulting view is nested two layers deep. When accessed from the SAS Windowing environment, the view renders the results displayed below.

SQL Code

PROC SQL;
  CREATE VIEW JOINED_VIEW AS
    SELECT V1.PRODNUM, V1.PRODNAME,
           V2.CUSTNUM, V2.INVQTY, V2.INVPRICE
      FROM WORKSTATION_PRODUCTS_VIEW V1,
					INVOICE_1K_VIEW V2
        WHERE V1.PRODNUM = V2.PRODNUM;
QUIT;

Results

In the next example, a third layer of view is nested to the previous view in order to find the largest invoice amount. In the next example, a view is constructed to find the largest invoice amount using the MAX summary function to compute the product of the invoice price (INVPRICE) and invoice quantity (INVQTY) from the JOINED_VIEW view.

When accessed from the SAS Windowing environment, the view produces the results displayed below.

SQL Code

PROC SQL;
  CREATE VIEW LARGEST_AMOUNT_VIEW AS
    SELECT MAX(INVPRICE*INVQTY) AS Maximum_Price
           FORMAT=DOLLAR12.2
           LABEL="Largest Invoice Amount"
      FROM JOINED_VIEW;
QUIT;

Results