Computed Columns Versus Computed Fields

When you define computed columns in the SQL Select painter, the value is calculated by the DBMS when the data is retrieved. When you define computed fields in the DataWindow painter, the value is calculated after the data has been retrieved.

When you create a DataWindow object, you can define computed columns and computed fields as follows:

In the SQL Select painter, define computed columns when you define the SELECT statement that will retrieve data into the DataWindow object.
In the DataWindow painter, define computed fields after you define the SELECT statement (or other data source).

When you define the computed column in the SQL Select painter, the value is calculated by the DBMS when the data is retrieved. The computed column's value does not change until data has been updated and retrieved again.

When you define the computed field in the DataWindow painter, the value of the column is calculated in the DataWindow object after the data has been retrieved. The value changes dynamically as the DataWindow object changes.

Note: If you want your DBMS to do the calculations on the server and you do not need the computed values to be updated dynamically, define the computed column as part of the SELECT statement.

If you need computed values to change dynamically, define computed fields in the DataWindow painter Design view.

Consider a DataWindow object with four columns: Part number, Quantity, Price, and Cost. Cost is computed as Quantity * Price.

Part#	Quantity	Price	Cost
101	100	1.25	125.00

If Cost is defined as a computed column in the SQL Select painter, the SELECT statement is:

SELECT part.part_num,
part.part_qty
part.part_price
part.part_qty * part.part_price
FROM part;

If the user changes the price of a part in the DataWindow object in this scenario, the cost does not change in the DataWindow object until the database is updated and the data is retrieved again. The user sees the changed price but the unchanged, incorrect cost.

Part#	Quantity	Price	Cost
101	100	2.50	125.00

If Cost is defined as a computed field in the DataWindow object, this is the SELECT statement (no computed column):

SELECT part.part_num
part.part_qty
part.part_price
FROM part;

The computed field is defined in the DataWindow object as Quantity * Price.

In this scenario, if the user changes the price of a part in the DataWindow object, the cost changes immediately.

Part#	Quantity	Price	Cost
101	100	2.50	250.00