* [[topbar |Table of Contents]] * [[intro:overview:what|Introduction]] * [[install:home|Installation]] * [[install:editions|Product Editions]] * [[install:requirements|System Requirements]] * [[install:platforms|Supported Platforms]] * [[install:guides:home|Installation Guides]] * [[install:licensing:home|Licensing]] * [[install:releases:home|Release Notes]] * [[admin:home|Administration]] * [[admin:admin:home|Administration Basics]] * [[admin:admin:architecture:home|Omnidex Architecture]] * [[admin:admin:applications:home|Building Applications]] * [[admin:basics:home|Omnidex Fundamentals]] * [[admin:basics:environments:home|Omnidex Environments]] * [[admin:basics:sqlengine:home|Omnidex SQL Engine]] * [[admin:basics:updates:home|Omnidex Updates]] * [[admin:basics:network:home|Network Services]] * [[admin:indexing:home|Omnidex Indexing]] * [[admin:indexing:concepts:home|Indexing Concepts]] * [[admin:indexing:indexes:home|Indexing Options]] * [[admin:indexing:creation:home|Index Creation]] * [[admin:indexing:strategies:home|Indexing Strategies]] * [[admin:indexing:activecounts:home|ActiveCounts]] * [[admin:indexing:powersearch:home|PowerSearch]] * [[admin:indexing:autocomplete:home|AutoComplete]] * [[admin:indexing:text:home|Omnidex Text]] * [[admin:features:home|Omnidex Features]] * [[admin:features:snapshots:home|Omnidex Snapshots]] * [[admin:features:grids:home|Omnidex Grids]] * [[admin:features:rollups:home|Rollup Tables]] * [[admin:features:geo:home|Geographic Searches]] * [[admin:features:expressioncols:home|Expression Columns]] * [[admin:features:segments:home|Omnidex Segments]] * [[admin:features:attach:home|Attaching Data]] * [[admin:features:export:home|Exporting Data]] * [[admin:optimization:home|Optimizing Queries]] * [[admin:optimization:plans:home|Query Plans]] * [[admin:optimization:caches:home|Dynamic Caches]] * [[admin:optimization:config:home|Configuring Omnidex]] * [[integration:home|Integration]] * [[integration:rdbms:home|Relational Databases]] * [[integration:rdbms:oracle:home|Oracle]] * [[integration:rdbms:sqlserver:home|SQL Server]] * [[integration:rdbms:mysql:home|MySQL]] * [[integration:rdbms:odbc:home|Generic ODBC]] * [[integration:rawdata:home|Raw Data Files]] * [[integration:rawdata:fixed:home|Fixed-length Files]] * [[integration:rawdata:delimited:home|Delimited Files]] * [[integration:rawdata:ost:home|Standalone Tables]] * [[dev:home|Development]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:overview|Overview]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[dev:connections:home|Connection Strings]] * [[dev:odbc:home|ODBC Interface]] * [[dev:jdbc:home|JDBC Interface]] * [[dev:debugging:home|Logging/Debugging]] * [[programs:home|Programs]] * [[programs:odxsql:home|OdxSQL]] * [[programs:odxnet:home|OdxNet]] * [[appendix:home|Appendix]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[http://www.omnidex.com/download/|Downloads]] * [[support:home|Support]]

~~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:optimization|Optimizing Queries]] ---- ==== 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:home|Prev]]** | **[[admin:features:expressioncols:optimization|Next]]** ====== Additional Resources ====== See also: {{page>:admin:features:see_also&nofooter&noeditbtn}} {{page>:bottom_add&nofooter&noeditbtn}}

 
Back to top
admin/features/expressioncols/declaration.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0