This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:expressioncols:declaration [2012/02/04 03:11] doc |
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 46: | Line 45: | ||
| ( | ( | ||
| "INDIVIDUAL" CHARACTER(12), | "INDIVIDUAL" CHARACTER(12), | ||
| - | "HOUSEHOLD" CHARACTER(12) omnidex, | + | "HOUSEHOLD" CHARACTER(12), |
| "NAME" CHARACTER(50), | "NAME" CHARACTER(50), | ||
| - | "GENDER" CHARACTER(1) omnidex, | + | "GENDER" CHARACTER(1), |
| - | "BIRTHDATE" ANSI DATE omnidex, | + | "BIRTHDATE" ANSI DATE, |
| "PHONE" CHARACTER(14), | "PHONE" CHARACTER(14), | ||
| "EMAIL" CHARACTER(60), | "EMAIL" CHARACTER(60), | ||
| - | "PHONE_AREACODE" CHARACTER(3) omnidex | + | "PHONE_AREACODE" CHARACTER(3) |
| as "substring(PHONE from 2 for 3)", | as "substring(PHONE from 2 for 3)", | ||
| - | "PHONE_PREFIX" CHARACTER(3) omnidex | + | "PHONE_PREFIX" CHARACTER(3) |
| as "substring(PHONE from 7 for 3)", | as "substring(PHONE from 7 for 3)", | ||
| - | "PHONE_SUFFIX" CHARACTER(4) omnidex | + | "PHONE_SUFFIX" CHARACTER(4) |
| as "substring(PHONE from 11 for 4)", | as "substring(PHONE from 11 for 4)", | ||
| constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"), | constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| Line 74: | Line 73: | ||
| ( | ( | ||
| "INDIVIDUAL" CHARACTER(12), | "INDIVIDUAL" CHARACTER(12), | ||
| - | "HOUSEHOLD" CHARACTER(12) omnidex, | + | "HOUSEHOLD" CHARACTER(12), |
| "NAME" CHARACTER(50), | "NAME" CHARACTER(50), | ||
| - | "GENDER" CHARACTER(1) omnidex, | + | "GENDER" CHARACTER(1), |
| - | "BIRTHDATE" ANSI DATE omnidex, | + | "BIRTHDATE" ANSI DATE, |
| "PHONE" CHARACTER(14), | "PHONE" CHARACTER(14), | ||
| "EMAIL" CHARACTER(60), | "EMAIL" CHARACTER(60), | ||
| Line 103: | Line 102: | ||
| 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: | 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" | create table "INDIVIDUALS" | ||
| physical "dat\individuals.dat" | physical "dat\individuals.dat" | ||
| ( | ( | ||
| "INDIVIDUAL" CHARACTER(12), | "INDIVIDUAL" CHARACTER(12), | ||
| - | "HOUSEHOLD" CHARACTER(12) omnidex, | + | "HOUSEHOLD" CHARACTER(12), |
| "NAME" CHARACTER(50), | "NAME" CHARACTER(50), | ||
| - | "GENDER" CHARACTER(1) omnidex, | + | "GENDER" CHARACTER(1), |
| - | "BIRTHDATE" ANSI DATE omnidex, | + | "BIRTHDATE" ANSI DATE, |
| "PHONE" CHARACTER(14), | "PHONE" CHARACTER(14), | ||
| "EMAIL" CHARACTER(60), | "EMAIL" CHARACTER(60), | ||
| - | "AGE" INTEGER omnidex | + | "AGE" INTEGER |
| as "$compare_dates(birthdate, current_date, 'YY')", | as "$compare_dates(birthdate, current_date, 'YY')", | ||
| - | "AGE_GROUP" CHARACTER(10) omnidex bitmap | + | "AGE_GROUP" CHARACTER(10) |
| as "case | as "case | ||
| when $compare_dates(birthdate, current_date, 'YY') between 0 and 17 | when $compare_dates(birthdate, current_date, 'YY') between 0 and 17 | ||
| Line 149: | Line 149: | ||
| ==== ==== | ==== ==== | ||
| - | **[[admin:features:expressioncols:declaration|Next]]** | + | **[[admin:features:expressioncols:home|Prev]]** | |
| + | **[[admin:features:expressioncols:optimization|Next]]** | ||