Progress
Database Design
Guide


Indexes and Unknown Values

If an index contains an unknown value (?), Progress sorts that value higher than any other value. When you define a unique index, Progress ensures its uniqueness. For example, if cust-num is a unique index, and there is already a cust-num with a value 10, Progress does not allow you to create a cust-num with the value 10. However,Progress does not prohibit users from entering any number of records with unknown values in index fields. You can prevent users from doing this by making the unique index fields mandatory.

EXAMPLES

Example 1: using the sports database, the following query will display all records where cust-num is > 10 because cust-num is an indexed field and the ? value will sort high in an indexed field:

FOR EACH cust WHERE cust-num >10 AND cust-num <= ? 

However, the query below will display ZERO records because cust-num is the chosen index for the query. Since zip is not the chosen index, the ? value will not sort high and the second part of the query will be false. No records are returned when one part of an AND is FALSE:

FOR EACH cust WHERE cust-num >10 AND cust-num <= ? AND zip >0 AND zip <? 

Example 2: the same rule can affect queries where the ? value is not explicitly used. Using the sports database, if you create three order records where order.cust-num = 1 and order-date = ?, then the following query will return the three records:

FOR EACH order WHERE order-date >= 1/1/97 

However, the following query will return NO records:

FOR EACH order WHERE order-date >= 1/1/97 AND cust-num = 1 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095