QUALIFY
The QUALIFY statement is used to qualify records using only the Omnidex
indexes. The actual data is not touched. The qualified records can be
retrieved with a SQL SELECT statement using the WITH 'ODXID' option.
A common reason for using a QUALIFY command instead of an SQL SELECT
statement is to obtain qualifying counts for each piece of criteria, without
incurring the overhead of retrieving the actual data.
The query can be "drilled down" and the previously qualified
count maintained by passing a leading SAMELIST operator with the criteria
of each subsequent call. Valid SAMELIST operators include AND, NOT, and
AND NOT.
The qualifying counts are stored in the status array. Three counts are
stored in the status array for each qualify: qualifying count, parent
count, and pre-intersect count.
- The qualifying count is the number of rows that would be returned
from a select statement with the specified criteria. It is the combined
qualified row count for all of the previous Qualify calls, as long as
a boolean operator was included with the criteria. If a leading Boolean
operator was not included with the criteria, this count pertains only
to the last Qualify call.
- The parent count only applies to qualifications made in child tables
where the indexes were installed in a domain or pre-joined index. The
parent count is the number of rows in the parent table that pertain
to the qualified rows in the child table. If the table qualified from
is not in the domain or a pre-joined index, this count is the same as
the qualifying count.
- The pre-intersect count is the number of rows matching the criteria
prior to intersecting this qualification with the previous qualification.
If there was not a leading Boolean operator, the pre-intersect count
is the same as the qualifying count.
Syntax
QUALIFY [(owner-table)] table WHERE [AND | NOT | AND NOT]
predicate
[ON [CURSOR] cursor] [WITH options]
QUALIFY
Required
[(owner_table)] table
Required. The owner_table is optional. The table parameter is the table
from which to qualify rows.
WHERE [AND | NOT | AND NOT] predicate
Required. Criteria used to qualify rows. A SAMELIST operator (AND, NOT,
or AND NOT) can precede the column/criteria pair. The SAMELIST operator
is not used on the first qualify statement.
ON [CURSOR] cursor
Optional - Specify which cursor to perform this command on.
[WITH options]
Optional - Specify options to be used for this command.
Options
Example
The first two qualify statements are completely separate and are included
to demonstrate the individual counts that qualify for the separate, given
criteria. This qualify statement shows that there are 25 customers in
the cities of Denver, Chicago, and Dallas.
>qualify customers where city in ('denver', 'chicago',
'dallas')
25 CUSTOMERS records qualify
This next qualify statement shows that there are a total of 272 customers
in the states of California, Colorado, and Texas.
>qualify customers where state in ('ca','co','tx')
272 CUSTOMERS records qualify
The next series of statements demonstrate the combined qualifying counts
and the retrieval the qualified rows.
First, qualify all the customers in the states of California, Colorado,
and Texas. 272 rows qualified.
>qualify customers where state in ('ca','co','tx')
272 CUSTOMERS records qualify
Next, qualify the customers in the cities of Denver, Chicago, and Dallas,
that were included in the previous 272 qualified rows.
>qualify customers where AND city in('denver','chicago','dallas')
14 CUSTOMERS records qualify
Remember that there are actually 25 customers in Denver, Chicago, and
Dallas, but because of the SAMELIST operator 'AND' preceding the criteria,
these 25 qualified records were intersected with the 272 qualified records.
Obviously, Chicago is not in any of the three states, so the number of
rows qualified by the city criteria dropped. Apparently, there are 10
customers in the city of Chicago.
To retrieve the 14 customers, we run a SELECT statement with the 'ODXID'
option.
>select company, city, state from customers with 'odxid'
COMPANY |
CITY |
STATE |
Fred Schmid TV and Appliances |
Denver |
CO |
Cognos Corporation |
Denver |
CO |
HMO Colorado, Inc. |
Denver |
CO |
National Conference State Legislatures |
Denver |
CO |
Anschutz Corporation |
Denver |
CO |
Blue Cross & Blue Shield of Colorado |
Denver |
CO |
Bock & Bock CPAS |
Dallas |
TX |
The Denver Post |
Denver |
CO |
Highland Park Independent School Dist. |
Dallas |
TX |
National Cable Television Institute |
Denver |
CO |
Texas Utilities Services, Inc. |
Dallas |
TX |
Random Access |
Denver |
CO |
Compusys Inc. |
Denver |
CO |
Interactive Software |
Denver |
CO |
It is also important to note that Denver, TX and Dallas, CO would also
have been qualified, if those cities existed in this database.
Since we did not include an ORDER BY clause in the SELECT statement,
the order the rows are returned is not guaranteed.
Top
|