Database Reference
In-Depth Information
As mentioned previously, another common use of views is for reformatting
retrieved data. The following
SELECT
statement (from Chapter 10, “Creating
Calculated Fields”) returns vendor name and location in a single combined cal-
culated column:
▼
Input
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
▼
Output
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
Now suppose that you regularly needed results in this format. Rather than per-
form the concatenation each time it was needed, you could create a view and
use that instead. To turn this statement into a view, you can do the following:
▼
Input
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
▼
Analysis
This statement creates a view using the exact same query as the previous
SELECT
statement. To retrieve the data to create all mailing labels, simply do
the following:
▼
Input
SELECT *
FROM vendorlocations;
