Overview | Declaring Expression-based Columns | Optimizing Queries

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:

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

Prev | Next

See also: