PseudoColumns
Pseudo-columns allows columns to be included in the environment file
that do not exist in the native database. Typically, these columns will
equate to literals, constants or expressions (e.g. AMOUNT * QUANTITY).
This feature is similar to the SQL Assembly Rules feature of OmniAccess;
however in this case, OmniAccess calculates the value for the column,
rather than the native database. Because of this, pseudo-columns are allowed
on both relational and non-relational databases.
Pseudo-columns must occur after native columns in the environment file;
placing them before the last native columns will produce an error when
compiling the environment file. After the normal clauses for a column,
such as data type and length, you may include an AS clause, which them
includes syntax identical to a select-item in oaselect. Any syntax for
a select-item is allowed, as long as it results in a single value and
is not an aggregate. Further, any columns referenced in the AS clause
must be previously declared in the same table.
Pseudo-columns are not differentiated by the Omnidex indexing kernel,
and therefore may be installed with MDK and ASK indexes. Select statements
that include pseudo-columns in their select-list, where clause and group
by clause are fully optimized by the OmniAccess optimizer.
The following examples demonstrate the use of pseudo-columns:
column "SUBTOTAL" datatype FLOAT length 8 as "AMOUNT
* QUANTITY"
column "GRANDTOTAL" datatype FLOAT length 8 as "SUBTOTAL
* TAX_RATE"
column "COMPANY_SDX" datatype C STRING length 40
as "$soundex(COMPANY)"
column "COUNTYSTATE" datatype CHARACTER length 5
as "COUNTY_CODE || STATE"
Indexing PseudoColumns
It is important to note that pseudo-columns will increase indexing time.
Indexing time is the sum of two numbers: the time it takes to index the
keywords in the data and the time it takes to serially read a table.
The former is a constant and is in no way affected by the use of pseudo-columns.
This is a simple formula that changes only by the total number of keyword
in the data. The latter will vary by the database management system. Some
DBMSs are considerably slower than others.
Pseudo-columns cause the table to be re-read multiple times, causing
multiple serial reads of that table. This can increase the serial read
time of this table by 50% to 100% for each pseudo-column.
Top
|