This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
admin:features:expressioncols:declaration [2012/02/04 02:55] doc created |
admin:features:expressioncols:declaration [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 9: | Line 9: | ||
[[admin:features:expressioncols:home|Overview]] | | [[admin:features:expressioncols:home|Overview]] | | ||
**[[admin:features:expressioncols:declaration|Declaring Expression-based Columns]]** | | **[[admin:features:expressioncols:declaration|Declaring Expression-based Columns]]** | | ||
- | [[admin:features:expressioncols:indexing|Indexing Expression-based Columns]] | | + | [[admin:features:expressioncols:optimization|Optimizing Queries]] |
- | [[admin:features:expressioncols:Optimization]] | + | |
---- | ---- | ||
Line 23: | Line 22: | ||
<code> | <code> | ||
create table "ORDERS" | create table "ORDERS" | ||
- | physical "dat\ord.dat" | + | physical "dat\orders.dat" |
( | ( | ||
"ORDER_NUMBER" CHARACTER(12), | "ORDER_NUMBER" CHARACTER(12), | ||
Line 39: | Line 38: | ||
=== Columns with String Expressions === | === 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, some of which parse a phone number, and others of which parse an email address: | + | 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: |
- | === Columns with Date Expressions === | + | <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> | ||
- | 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: | + | === 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> | ||
- | === Columns with Logic Expressions === | ||
=== Restrictions === | === Restrictions === | ||
Line 61: | Line 149: | ||
==== ==== | ==== ==== | ||
- | **[[admin:features:expressioncols:declaration|Next]]** | + | **[[admin:features:expressioncols:home|Prev]]** | |
+ | **[[admin:features:expressioncols:optimization|Next]]** | ||