Database Reference
In-Depth Information
The result is:
As desired, only customers who live in Washington are shown in this view. This limitation
is not obvious from the results because State is not included in the view. This characteristic
is good or bad, depending on the use of the view. It is good if this view is used in a setting in
which only Washington customers matter; it is bad if the view miscommunicates that these
customers are the only View Ridge Gallery customers.
Using SQL Views to Display Results of Computed Columns
Another purpose of views is to show the results of computed columns without requiring the
user to enter the computation expression. For example, the following view combines the
AreaCode and PhoneNumber columns and formats the result:
/* *** SQL-CREATE-VIEW-CH07-04 *** */
CREATE VIEW CustomerPhoneView AS
SELECT LastName AS CustomerLastName,
FirstName AS CustomerFirstName,
('(' + AreaCode + ')' + PhoneNumber) AS CustomerPhone
FROM
CUSTOMER;
When the view user executes the SQL statement:
/* *** SQL-Query-View-CH07-04 *** */
SELECT
*
FROM
CustomerPhoneView
ORDER BY
CustomerLastName, CustomerFirstName;
the results
2
will be:
2
As you might expect, different DBMS products use a different operator for the concatenation operation in the
CustomerPhoneView definition. For example, in Oracle Database, the plus sign (
+
) must be replaced by double
vertical bars (
∙
) for string concatenation, while MySQL uses the CONCAT() string function. See the example in
Chapter 2 and the documentation for your DBMS for more details.
Search WWH ::
Custom Search