Overview | Declaring Expression-based Columns | Indexing Expression-based Columns | Optimization

Expression-based columns are declared in the Environment File as though they were a normal column in a table. They must always be declared at the end of the column list, after all of the columns that do reside in the underlying database. Expression-based columns have names and datatypes just like normal columns, but they additionally have an 'AS “SQL-expression”' clause.

Expression-based columns can use arithmetic expressions to calculate a new value based on other columns in the table. The following example shows a table that calculates a TOTAL column based on the QUANTITY, UNIT_COST, TAX_RATE and COMMISSION columns:

create table          "ORDERS"
 physical             "dat\ord.dat"
 (
  "ORDER_NUMBER"      CHARACTER(12),
  "ORDER_DATE"        ANSI DATE,
  "QUANTITY"          INTEGER,
  "UNIT_COST"         INTEGER,
  "TAX_RATE"          DOUBLE,
  "COMMISSION"        DOUBLE,
  "TOTAL"             DOUBLE,
    as "((QUANTITY * UNIT_COST) * TAX_RATE) + COMMISSION)",
 )
 in                   "simple.xml";

Expression-based columns can use string expressions to extract or concatenate portions of other fields. The following example shows several columns, some of which parse a phone number, and others of which parse an email address:

Expression-based columns can use date expressions to extract, compare or calculate based on dates. The following example shows columns that calculate and individual's age:

The following restrictions exist for expression-based columns:

• Expression-based columns cannot be referenced in table constraints
• Expression-based columns cannot reference aggregate functions
• Expression-based columns cannot reference columns from other tables
• Expression-based columns cannot reference other expression-based columns.

Next

See also: