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 page of the column property sheet. For more complex expressions, use the SQL Editor available through the Edit tool found on the same page.

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 in the diagram to display the table property sheet.
  2. Click the Columns tab to display the list of columns.
  3. Click a blank line in the list.

    or

    Click the Add a Row tool.

    An arrow appears at the beginning of the line.
  4. Type a column name and a column code.
  5. Click Apply.
  6. Double-click the arrow at the beginning of the new column to display the column property sheet.
  7. Select the Computed check box.
  8. Click the Detail tab.
  9. Enter an expression in the Computed Expression pane.

    or

    Click the Edit tool on the right side of the Computed expression pane.


    The SQL Editor dialog box appears.
  10. Create the expression that will define the computed column.


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

    The expression appears in the Computed Expression pane.
  12. Click OK in each of the dialog boxes.

 

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