This shows you the differences between two versions of the page.
|
admin:features:expressioncols:declaration [2012/02/04 06:03] doc |
admin:features:expressioncols:declaration [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Administration: Omnidex Features ====== | ||
| - | |||
| - | ===== Expression-based Columns ===== | ||
| - | |||
| - | [[admin:features:expressioncols:home|Overview]] | | ||
| - | **[[admin:features:expressioncols:declaration|Declaring Expression-based Columns]]** | | ||
| - | [[admin:features:expressioncols:indexing|Indexing Expression-based Columns]] | | ||
| - | [[admin:features:expressioncols:Optimization]] | ||
| - | ---- | ||
| - | |||
| - | ==== Declaring Expression-based Columns ==== | ||
| - | |||
| - | Expression-based columns are declared in the [[admin:basics:environments:home|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. | ||
| - | |||
| - | === Columns with Arithmetic Expressions === | ||
| - | |||
| - | 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: | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | === Columns with String Expressions === | ||
| - | |||
| - | 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: | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | === Columns with Logic Expressions === | ||
| - | |||
| - | 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: | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | === Columns with Date Expressions === | ||
| - | |||
| - | 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: | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | === Restrictions === | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | |||
| - | ==== ==== | ||
| - | **[[admin:features:expressioncols:declaration|Next]]** | ||
| - | |||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:features:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||