Chapter 3 Building Physical Diagrams

Creating a computed column

A computed column is a column whose content is computed from an expression using values from other columns in the table. The computed column is then filled with the results.

Simple computed expressions can be entered directly in the Computed Expression pane on the Detail tab of the column property sheet. For more complex expressions, use the SQL Editor available through the Edit tool found on the same tab.

Computed columns are not available in all DBMS.

Example

Assume that you want to automatically fill a column with the total sales of widgets. To do this, you can create a computed column that will use the number of widgets multiplied by the widget price:

Column name Contents Action on data
Number of widgets Number of widgets sold —
Widget price Price of widgets when sold —
Widget sales Total widget sales Computed by multiplying the first two columns

While our example is very simple, the SQL Editor allows you to define very complex computed column expressions.

For more information on the SQL Editor, see Using SQL tools in chapter Physical Data Model Basics.

Steps To create a computed column:

  1. Double-click a table to open its property sheet, and click the Columns tab.
  2. Click the Add a Row tool, and then click the Properties tool to open the property sheet for the new column.
  3. On the General tab, select the Computed checkbox, and then click the Detail tab.
  4. Enter an expression in the Computed Expression box to define the computed column. Alternatively,you can click the Edit with SQL Editor tool to use the SQL Editor.


    In our example, we use the asterisk (*) as an arithmetic operator to multiply the number of widgets by their price.
  5. Click OK to return to the column property sheet.

    The expression is displayed in the Computed Expression pane.
  6. Click OK in each of the dialog boxes.

 

Copyright (C) 2007. Sybase Inc. All rights reserved.