This shows you the differences between two versions of the page.
admin:features:expressioncols:declaration [2012/02/04 02:55] doc created |
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\ord.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, some of which parse a phone number, and others of which parse an email address: | ||
- | |||
- | === 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 and individual's age: | ||
- | |||
- | |||
- | |||
- | === Columns with Logic Expressions === | ||
- | |||
- | === 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}} |