Database Reference
In-Depth Information
10
In this chapter, you learn what calculated fields are, how to create them, and how
to use aliases to refer to them from within your application.
Data stored within a database's tables is often not available in the exact format
needed by your applications. Here are some examples:
■
You need to display a field containing the name of a company along
with the company's location, but that information is stored in separate
table columns.
■
City, state, and ZIP Code are stored in separate columns (as they
should be), but your mailing label printing program needs them
retrieved as one correctly formatted field.
■
Column data is in mixed upper- and lowercase, and your report needs
all data presented in uppercase.
■
An order items table stores item price and quantity but not the
expanded price (price multiplied by quantity) of each item. To print
invoices, you need that expanded price.
■
You need total, averages, or other calculations based on table data.
In each of these examples, the data stored in the table is not exactly what your
application needs. Rather than retrieve the data as it is and then reformat it
within your client application or report, what you really want is to retrieve
converted, calculated, or reformatted data directly from the database.
This is where calculated fields come in. Unlike all the columns we retrieved
in the chapters thus far, calculated fields don't actually exist in database
tables. Rather, a calculated field is created on-the-fly within a SQL
SELECT
statement.
