Multiple records with the same key value

Tables that contain records with the same key value must be handled cautiously. For example, in the table containing customer names, shown in the following illustration, the key is the customer’s last name. Several customers have the last name “Smith”. Thus, several records have the same key value.

Adding records

To allow multiple records to have the same key value, you must choose the appropriate options in the Key Definition window when you create keys for the table. For Pervasive.SQL and c-tree tables, mark the Duplicates option. For SQL tables, be sure the Unique option is not marked. If you haven’t chosen the appropriate options, an error indicating a duplicate record will occur when you add records with the same key value to a table.

Retrieving records

It’s more difficult to retrieve records that have the same key value from a table. The standard practice of setting the table buffer to the key value and then using a get or change statement won’t work because the database manager can’t guarantee which record will be retrieved. For example, if the table buffer for the table shown in the previous illustration was set to “Smith” and a get or change statement was used to retrieve a record, the table buffer could contain the record for Alan, Maria, Bob or Sharon Smith. There is no way of knowing which record will be read.

Two methods can be used to reliably retrieve records that have the same key value.

Start at the beginning of the table using the get first or change first statement. Then use get next or change next statements to read all of the records in the table. Be sure you’re using the same key for the get first or change first and get next or change next statements.

This method will read all of the records in the table, including those that have the same key value. However, there is no way of determining the order in which the records having the same key value will be read.

Use the range start statement to set the beginning of the range to the duplicate key value, and use the range end statement to set the end of the range to the duplicate key value. Use the get first or change first statement to retrieve the first record in the range. Then use the get next or change next statement to retrieve successive records from the range. Be sure you’re using the same key for the range and get or change statements.

This method will read all of the records that have the same key value specified using the range statements. However, there is still no way of determining the order in which the records having the same key value will be read.

The following script reads all of the “Smiths” from the example table. The range statements allow the table to access only records with the key value “Smith.” The get first and get next statements read all of records from the range in the table. Note that the same key is used for all of the range and get statements.

{Clear any existing ranges and set up the new range.}
range clear table Customers;
'Last Name' of table Customers = "Smith";
range start table Customers by number 1;
range end table Customers by number 1;
{Read the records.}
get first table Customers by number 1;
while err() <> EOF do
  get next table Customers by number 1;
end while;