SQL Commands

QUALIFY

QUALIFY [(owner-table)] table WHERE predicate [ON [CURSOR] cursor] [WITH options]

The Qualify statement is used to obtained qualifying counts for specific criteria without incurring the overhead of retrieving the actual data. Qualify is used in place of a SQL SELECT statement, passing criteria for a column with each statement. The query can be "drilled down" and the previously qualified count maintained by passing a leading Boolean operator with the criteria of each subsequent call.

The specific counts are generated with each Qualify call: qualifying count, parent count, preintersect 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.

Note, that although QUALIFY does not return the data, it is still possible to retrieve the data, if desired, by running a SELECT command with the WITH 'ODXID' option after the QUALIFY statements are complete. This "Selects" the previously qualified rows and returns them in the same manner as a normal SQL SELECT statement would (JDBC ResultSet, etc...).

Syntax

QUALIFY [(owner-table)] table WHERE 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 predicate

Required. Criteria used to qualify rows.

ON [CURSOR] cursor

Optional - Specify which cursor to perform this command on.

[WITH options]

Optional - Specify options to be used for this command.

top

OdxSQL 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 city in ('and denver', 'chicago', 'dallas')
14 CUSTOMERS records qualify

Remember that there are actually 25 customers in Denver, Chicago, and Dallas, but because of the leading Boolean operator 'AND' in 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'

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 rows are returned in an undefined order.

top