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\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)     omnidex,               
  "NAME"              CHARACTER(50),                
  "GENDER"            CHARACTER(1)      omnidex,                    
  "BIRTHDATE"         ANSI DATE         omnidex,                
  "PHONE"             CHARACTER(14),               
  "EMAIL"             CHARACTER(60),                  
  "PHONE_AREACODE"    CHARACTER(3)      omnidex
    as "substring(PHONE from 2 for 3)",
  "PHONE_PREFIX"      CHARACTER(3)      omnidex
    as "substring(PHONE from 7 for 3)",
  "PHONE_SUFFIX"      CHARACTER(4)      omnidex
    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)     omnidex,               
  "NAME"              CHARACTER(50),                
  "GENDER"            CHARACTER(1)      omnidex,                    
  "BIRTHDATE"         ANSI DATE         omnidex,                
  "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)     omnidex,               
"NAME"              CHARACTER(50),                
"GENDER"            CHARACTER(1)      omnidex,                    
"BIRTHDATE"         ANSI DATE         omnidex,                
"PHONE"             CHARACTER(14),               
"EMAIL"             CHARACTER(60),                  
"AGE"               INTEGER           omnidex
  as "$compare_dates(birthdate, current_date, 'YY')",
"AGE_GROUP"         CHARACTER(10)     omnidex bitmap 
  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:

• 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: