Creating a Computed Column

Go Up to Creating a Table (Embedded SQL Guide)

A computed column is one whose value is calculated when the column is accessed at run time. The value can be derived from any valid SQL expression that results in a single, non-array value. Computed columns are “virtual” in that they do not correspond to data that is physically stored in the database. The values are always generated during the SELECT query. They have to be generated dynamically in case the values they are based on change.

To create a computed column, use the following column declaration syntax in CREATE TABLE:

col COMPUTED [BY] (<expr>)

The expression can reference previously defined columns in the table. For example, the following statement creates a computed column, FULL_NAME, by concatenating two other columns, LAST_NAME, and FIRST_NAME:

EXEC SQL
CREATE TABLE EMPLOYEE
(
. . .
FIRST_NAME VARCHAR(10) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
. . .
FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME)
);

For more information about COMPUTED BY, see the Data Definition Guide.