Understanding Calculated Fields


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 separated 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.

New Term

Field Essentially means the same thing as column and often is used interchangeably, although database columns are typically called columns and the term fields is normally used in conjunction with calculated fields.


It is important to note that only the database knows which columns in a SELECT statement are actual table columns and which are calculated fields. From the perspective of a client (for example, your application), a calculated field's data is returned in the same way as data from any other column.

Tip

Client Versus Server Formatting Many of the conversions and reformatting that can be performed within SQL statements can also be performed directly in your client application. However, as a rule, it is far quicker to perform these operations on the database server than it is to perform them within the client because Database Management Systems (DBMS) are built to perform this type of processing quickly and efficiently.





MySQL Crash Course
MySQL Crash Course
ISBN: 0672327120
EAN: 2147483647
Year: 2004
Pages: 214
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net