Declaration
function Query( sExpression ): LongInt;
Description
Sets an ultra high speed filter. Query uses open indexes to determine whether a filter can be constructed based upon index keys rather than the evaluation of data extracted from the table.
The query optimization algorithms used may result in filter setting and data extraction hundreds of times faster than standard xBase filters. If query optimization cannot be performed, a standard filter is set (see SetFilter).
An Query filter respects current filter settings and the setting of SetDeleted.
For remote tables accessed via the Apollo Database Server, the TApolloQuery component's SQL property supports the use of SELECT statements for retrieving result sets that can be navigated much quicker than TApolloTable.Query.
Parameters
sExpression: A standard xBase expression that defines the query filter conditions. If Query determines that the expression contains field references that match fields used to construct current open indexes, query optimization is performed.
For query optimization to work, a field referenced in the conditional expression must be the first field referenced in an index expression (i.e., the leftmost field if there is more than one).
For example, the benchmark test for Query used the expression below to extract a 3 record subset from a 10,000 record table in less than one second.
lRec := ApTbl.Query('STATE="CA" .and. (CITY="San" .or. CITY="Los")');
The table had indexes open on the fields CITY and STATE.
If xBase functions qualify a field in the index constructing expression, they should also be used in the query expression. For example, if the index expression is 'upper(country)', then the query expression should be in the form 'upper(country) = "GERMANY"'. If the query expression was passed as 'country = "GERMANY"' without the upper conversion function, the query would be set up properly and return the correct results, but it would NOT evaluate a record addition or change correctly if the user entered 'Germany' as the country. New records and edited records use the table data rather than the index key to determine whether or not the record is added to the query subset.
An index used in query optimization does not have to be the controlling index and the query conditional expression may use fields that reference more than one index (as in the STATE, CITY example above).
Query sets a standard filter if it cannot use open indexes to evaluate the passed condition(s). Query may use a mix of query optimization and standard filters (e.g., 'STATE = "CA" .and. AGE > 25' where AGE is not indexed).
Conditional expressions that contain references to aliased fields in tables linked to the current work area via SetRelation are not optimized.
Result
If a query is successful, the record pointer is positioned to the first record in the subset and the record number is returned. If the query is not successful, zero is returned.
Speed Tips
The fastest queries are built using
an equal condition (= or ==)
a less than (<) or less than or equal condition (<=)
a greater than (>) or greater than or equal to condition (>=)
(.NOT. field = condition) is much faster than (field <> condition)
Clearing a Query
To clear a query, pass the query expression as a NULL string ('' or #0). Clearing a query also clears filters set with SetFilter.
Constructing Query Expressions
If using values contained in variables to construct a query expression, the values must become part of the xBase expression string. For example, if a state code is solicited from the user and stored in a variable named 'statecode', the query expression would be constructed as follows:
lRec := ApTbl.Query( 'state = "' + statecode + '"' );
Partial and Exact Matching
If searching for all customers with a first name of "Sam" you might use the following query expression:
'FIRST = "Sam"'
However, this may also return records where the first name is Samantha, Samuel, Sampson, or any other name the has the same three first letters as the query value. This is valuable for searching for a partial match. But, if you are wanting to find only those records where the FIRST name field is exactly "Sam" and not just names that start with Sam, you must pad the search string to the width of the field, like this:
FIRST = PadR( "Sam", 20, " " )
Or, when the search value is stored in a variable (sVal):
FIRST = PadR( "' + sVal + '", 20, " " )
The PadR function is one of the supported xBase functions built in to Apollo's xBase Expression Engine for use within query and index expression like this.
Logical Operator Precedence
Logical operator precedence is .NOT., .AND., and finally .OR. Query expressions with more than two elements using different logical operators to join the elements must be carefully constructed by observing the precedence rules.
For example, suppose we want a strange query subset that includes all persons whose last name begins with "B" or everyone who is married. These people must live in a city that begins with the letter 'C'.
'upper(last) = "B" .or. married .and. city = "C"'
This will give us the wrong answer. Because .AND. has a higher precedence than .OR., we will first get all married people who live in a city that begins with 'C'. Once this bitmap has been built, all the persons whose last names begin with 'B' are added to the subset - whether or not they live in a city that begins with 'C'. The correct form of the expression is
'(upper(last) = "B" .or. married) .and. city = "C"'
This changes the evaluation order and forces the first two bitmaps to be evaluated first with the .OR. operator.
Logical Negation
In Boolean terms, .NOT. has the highest precedence. Apollo adheres strictly to this rule. An expression that is given as
'dtos(eventdate) = dtos(date) .and. .not. status = "H"'
(which is perfectly valid in most xBase dialects) will be evaluated as
'.not. (dtos(eventdate) = dtos(date) .and. status = "H")'
which is not what is intended. If using .NOT., always parenthesize each expression in the query to ensure that evaluation proceeds according to intention. The expression above should be passed as
'(dtos(eventdate) = dtos(date)) .and. (.not. status = "H")'
In the first case, the pcode generated is of the form
field1;value1;=: field2;value2;=: &!
which creates a query bitmap for expression 1, another query bitmap for expression 2, then ands the two bitmaps and finally negates them. This is not what we want. By placing the .not. within the parenthetical expression surrounding the second condition, Apollo generates pcode of the form
field1;value1;=: field2;value2;=: !&
which creates a query bitmap for expression 1, another query bitmap for expression 2, negates the second bitmap, and then ands the two bitmaps. This is what is intended.
Index Types
Query expression equations must be posed in terms of the data type of the index.
1. Character types require a string enclosed in double quotation marks.
upper(lastname) = "SMITH"
2. Date types require a CTOD function. The date string must be formatted according to the settings of SetDateFormat and SetCentury.
hiredate > ctod("05/15/94")
3. Logical types simply use the field name.
married
4. Numeric types require a number (with no quotes).
age > 25
QKeyVal
QKeyVal is a special xBase function in TApolloTable designed for use with query optimization.
When building query expressions it is sometimes necessary to
(1) refer to the value of the key directly;
(2) target a specific index for optimization.
QKeyVal("tagname") allows us to do these things.
For example, suppose we had an index that was constructed with SUBSTR(DTOS(startdate),5), which indexes on the month and day of an xBase date. Now, if we attempt to create a query bitmap with this index using
ApTbl.Query(SUBSTR('DTOS(startdate,5)) > "0621"')
we will get erroneous results (usually zero records) even though QueryTest returns FULL optimization possible..
"Why?" you ask. When the query engine attempts to optimize an expression, it extracts the first field name it encounters in the expression and checks each index expression to see if it is also the first field referenced in the key construct. If so, when it comes time to build the bitmap, it replaces the field reference in the expression with the key value. Since the Apollo query optimizer allows the construction of complex xBase expressions on either side of an equation, it leaves the expression surrounding the field reference intact and executes it.
The result? If field "startdate" in the example above contained "19940630" then the key for that record would be "0630". The query engine would execute the expression as
SUBSTR(DTOS("0630", 5))
which would return a NULL value and no records would satisfy the condition. We can't simply say 'startdate > "0621"' because a date type cannot be compared to a string when the expression is tested for correct syntax.
We can properly execute the query by using a reference to the actual key value here instead of the expression used to build the index.
ApTbl.Query('QKeyVal("startdate") > "0621"')
When using QKeyVal it is necessary to specify the tag name of the index because there is no "QKeyVal" expression allowed in an index expression so there is no way to check if the query can be optimized. This also provides a sideline benefit in that we can now target a specific index to be used for optimization. This is valuable if a conditional index exists in the current index list that has the same key expression as a master index. We don't know which one the query engine will use (and the results will be different depending on the index used to build the query bitmap).
We can use this to advantage if we wish to construct a bitmap from a non-optimizable condition and then switch index orders. Why would we want to do this? For speed. A non-optimized query sets a standard filter. A Query bitmap can be hundreds of times faster than a standard filter in defining and traversing the required subset.
For example, suppose we wish to create a bitmap for all persons in California and there is no index on state (but there are indexes on last name and company).
// create a temporary conditional index
ApTbl.Index( 'sxtemp.idx', 'last', IDX_NONE, False, 'state = "CA"');
// create a bitmap using this index with a condition that satisfies all
// keys in the conditional index (in this case no last name starts with "1")
lRec := ApTbl.Query('QKeyVal("sxtemp.idx") <> "1"');
// remove the conditional index
ApTbl.IndexClose;
DeleteFile( 'sxtemp.idx' );
// now we can view the subset in the order of any existing index
iPrev := ApTbl.SetOrder( ApTbl.TagArea('company'));
NOTE: When constructing the query bitmap with an eye towards including all of the records in the conditional index, use a NOT EQUAL (<>) condition which will be True for every key in the index. Do not use a .NOT. operator because this inverts the entire bitmap and will include records not found in the conditional index subset.
QKeyVal Rules
1. The tag name passed as a parameter to QKeyVal is the index name with extension (but not with path) in the case of NTX or IDX files. With CDX and NSX indexes, it is the tag name passed when the tag was created.
2. QKeyVal only extracts character type keys. Numeric, logical, and date keys cannot be extracted with QKeyVal.
See Also
DBFilter, FilterDlg, QueryRecCount, QueryTest, SetDeleted, SetFilter, xBase Expression Engine, xBase Functions Supported