This is an old revision of the document!
The Omnidex QUALIFY statement is an advanced statement used for testing and for legacy applications requiring access to Omnidex pre-intersect counts. The use of the QUALIFY statement has been replaced by adding features to the SELECT statement.
QUALIFY [(owner-table_spec)] table_spec WHERE [AND | NOT | AND NOT] predicate
[ON [CURSOR] cursor]
[WITH options]
Required. The owner_table is optional. The table parameter is the table from which to qualify rows.
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.
Optional - Specify which cursor to perform this command on.
Optional - Specify options to be used for this command.
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.