Criteria Conditioning
Rules for handling criteria strings in a WHERE clause:
Rule 1 - Sentinel Character
The Omnidex Sentinel character is used in the right operand of a WHERE
clause predicate and instructs Omnidex to consider the following criteria
as Omnidex specific, as opposed to SQL specific. For example, company
= '% systems or software' is the same as company
= 'systems' or company = 'software'.
The Omnidex Sentinel character is determined by the ENVIRONMENT ...OMNIDEX
SENTINEL clause in the environment catalog. The default is %.
If the sentinel character is the leading character in the string and the
predicate operator is = or IN, the criteria, minus the sentinel character,
are submitted directly to Omnidex.
Rule 2 - Special Characters
Fields installed with the KW (Keyword) option will be processed as follows:
Leading and trailing characters that would be removed during indexing,
are removed from the criteria string.
When using the =, !=, LIKE and IN operators, embedded characters that
are treated as keyword delimiters during indexing, are changed to a space.
The space is the implied AND operator within Omnidex which enables a multiple-keyword
search. For each keyword, leading and trailing characters that would be
removed during indexing are removed and each keyword is processed individually
according to the other rules.
When using the >, <, >=, <=, and BETWEEN operators, the leading
characters that are removed during indexing are removed. Criteria containing
multiple keywords are quoted and regarded as a single keyword. Multiple-keyword
searches are not supported with these operators.
Rule 3 - Quoted Operators
Characters that are regarded as operators by the underlying indexing routines,
are treated as literals and quoted as needed. Reserved words like AND,
OR, NOT and TO are also quoted.
Rule 4 - Wildcard Characters
When the criteria contains a wildcard character (default *, ?, #) and
is used with the >, <, >=, <= and BETWEEN operators, the criteria
are quoted, causing the wildcard characters to be treated as literal characters.
Wildcards are not supported with these operators.
Rule 5 - % and _ (Percent and underscore) Characters
When used with the LIKE operator, the % and _ characters are ANSI SQL
wildcard characters and are therefore converted to the corresponding Omnidex
wildcard characters * and ? (or the corresponding custom wildcard characters
as defined in the environment catalog).
When used with = (equals), % is the default Omnidex Sentinel character.
They are treated as literal characters when used with any other operator.
Rule 6 - Blank Criteria
Criteria consisting of spaces or an empty string, is changed to a single
space and quoted.
Rule 7 - Double Quotes
Double-quotes tell Omnidex to treat characters as literals. Therefore:
When all or part of the criteria string is double-quoted, Omnidex will
process ANSI wildcards according to rule 5, provided the wildcards are
outside of the quotes.
Omnidex will not process special characters according to rules 3 and 4.
Rule 8 - $SOUNDEX
If the criteria is from the $SOUNDEX function and the index being used
has been installed with the ;SX option, the indexing token '!' will be
appended to the original string from the $SOUNDEX function. The presence
of this token will cause the indexing layer to process a Soundex search
Top
|
