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;
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