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\orders.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 which parse a phone number into its components:
create table "INDIVIDUALS" physical "dat\individuals.dat" ( "INDIVIDUAL" CHARACTER(12), "HOUSEHOLD" CHARACTER(12), "NAME" CHARACTER(50), "GENDER" CHARACTER(1), "BIRTHDATE" ANSI DATE, "PHONE" CHARACTER(14), "EMAIL" CHARACTER(60), "PHONE_AREACODE" CHARACTER(3) as "substring(PHONE from 2 for 3)", "PHONE_PREFIX" CHARACTER(3) as "substring(PHONE from 7 for 3)", "PHONE_SUFFIX" CHARACTER(4) as "substring(PHONE from 11 for 4)", constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"), constraint IND_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", constraint IND_GENDER_FK foreign ("GENDER") references "GENDERS", ) in "simple.xml";
Expression-based columns can use logic through the CASE function to conditionally assign values to a column. The following example shows two columns that parse the components of an email address:
create table "INDIVIDUALS" physical "dat\individuals.dat" ( "INDIVIDUAL" CHARACTER(12), "HOUSEHOLD" CHARACTER(12), "NAME" CHARACTER(50), "GENDER" CHARACTER(1), "BIRTHDATE" ANSI DATE, "PHONE" CHARACTER(14), "EMAIL" CHARACTER(60), "EMAIL_MAILBOX" CHARACTER(60) quicktext as "case when position('@' in EMAIL) > 0 then substring(EMAIL from 1 for position('@' in EMAIL) - 1) else '' end", "EMAIL_DOMAIN" CHARACTER(60) quicktext as "case when position('@' in EMAIL) > 0 then substring(EMAIL from position('@' in EMAIL) + 1) else '' end", constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"), constraint IND_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", constraint IND_GENDER_FK foreign ("GENDER") references "GENDERS", ) in "simple.xml";
Expression-based columns can use date expressions to extract, compare or calculate based on dates. The following example shows columns that calculate an individual's age and age group:
create table "INDIVIDUALS" physical "dat\individuals.dat" ( "INDIVIDUAL" CHARACTER(12), "HOUSEHOLD" CHARACTER(12), "NAME" CHARACTER(50), "GENDER" CHARACTER(1), "BIRTHDATE" ANSI DATE, "PHONE" CHARACTER(14), "EMAIL" CHARACTER(60), "AGE" INTEGER as "$compare_dates(birthdate, current_date, 'YY')", "AGE_GROUP" CHARACTER(10) as "case when $compare_dates(birthdate, current_date, 'YY') between 0 and 17 then '0-17' when $compare_dates(birthdate, current_date, 'YY') between 18 and 29 then '18-29' when $compare_dates(birthdate, current_date, 'YY') between 30 and 39 then '30-39' when $compare_dates(birthdate, current_date, 'YY') between 40 and 49 then '40-49' when $compare_dates(birthdate, current_date, 'YY') between 50 and 59 then '50-59' else '60+' end", constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"), constraint IND_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", constraint IND_GENDER_FK foreign ("GENDER") references "GENDERS", ) in "simple.xml";
The following restrictions exist for expression-based columns:
See also: