![[spacer]](1p.gif){kind=small}
The range statement is used to select a portion of a table to use. A range can reduce the number of records that must be accessed in order to accomplish a task, increasing the speed and efficiency of your application.
range [start| end | clear] table table_name {by key_name | by number expr}{, inclusive | , exclusive}
• start | end | clear – Identifies the purpose of this particular range command. The start keyword sets the beginning of the range to the current values in the table buffer. The end keyword sets the end of the range to the current values in the table buffer. The clear keyword clears any range set for the key, but doesn’t affect the table buffer.
• table table_name – The name of the table the range will be applied to.
• by key_name | by number expr – Identifies the key this range will be associated with. Keys can be identified by their name (by key_name) or by their numeric position in the table definition (by number expr). This parameter isn’t used when the clear keyword is used. If no key is specified, the first key is used.
• inclusive | exclusive – In the range end statement, this keyword specifies how the range will be evaluated. This option applies only when the SQL database manager is being used; it will be ignored for the other database managers.
If the SQL database manager is being used, and the inclusive keyword is included, an inclusive range will be generated. If the exclusive keyword is included, a pure exclusive range will be generated. If a range type isn’t specified, Dexterity will decide which type of range to use, based upon how the key segments for the range have been set.
The selected range of the table will be treated as an entire table. For instance, a get first statement that includes the same by key_name or by number expr clause that the range statement used, will return the first record in the range.
A range is associated with a key. The range will be used only when the table is accessed by the key with which the range is associated. All other keys will access the entire table.
|
You can define only one range at a time for a given table buffer, regardless of how many keys have been defined for the table. Each key can’t have its own range. To use a new range to access the table, you must clear the first range using the range clear statement. |
|
You can use the Table_IsRangeSet() function to find out whether a range is set for a table. |
A range is cleared by the range clear statement or closing the table. If you issue a range statement without first clearing an existing range, you’ll be able to access data from the old range only.
If a key is composed of several segments, you can create ranges based upon several key segments. The clear field and fill statements are often used when setting ranges for multisegment keys. Typically, the first several corresponding key segments of the range start and range end are set to the same values. Then the remaining key segments are cleared and filled.
How a range is evaluated depends upon the database manager used for the table, how the key segments for the table have been set, and whether the inclusive or exclusive keywords have been included in the range end statement.
For the Pervasive.SQL and c-tree database managers, ranges are always evaluated inclusively. Including the inclusive or exclusive keyword has no effect on the range.
The following flowchart describes the process used to evaluate an inclusive range.
For the SQL database manager, how the range is evaluated depends upon whether the inclusive or exclusive keyword is included in the range end statement. If the inclusive keyword is included, the range will be an inclusive range, just like the one produced for c-tree or Pervasive.SQL. If the exclusive keyword is included, the range will be evaluated exclusively.
*The following flowchart describes the process used to evaluate an exclusive range.
|
Evaluating inclusive ranges for SQL is significantly slower than evaluating exclusive ranges. For this reason, we recommend that you use inclusive ranges for SQL tables only when absolutely necessary. |
If the SQL database manager is used and neither the inclusive nor the exclusive keyword is included in the range end statement, Dexterity will determine the type of range used based upon how the key segments for the range have been set. If the range is “well-behaved” or Dexterity can alter the range to make it “well-behaved,” an exclusive range will be generated. Otherwise, an inclusive range will be generated.
Starting from the leftmost key segment and working to the right, a “well-behaved” range has the following characteristics:
|
It is desirable for a range to be “well-behaved” because “well-behaved” ranges produce the same results for all database managers. |
If Dexterity analyzes a range and finds the range to be “well-behaved” except that the rightmost key segments haven’t been cleared and filled, those segments will be automatically cleared and filled and an exclusive range will be produced.
You can use the FHCheckRanges defaults file setting to log the instances when Dexterity decides which type of range to use. The log will list all instances when Dexterity detected a range that was not “well-behaved” and whether Dexterity used an exclusive or inclusive range. The log will not include ranges where the inclusive or exclusive option was used in the range end statement.
If you set a range and then set the key value out of the specified range and issue an unqualified get, change or edit table statement (one that doesn’t include the prev, next, first or last keywords), you will receive an EOF error. However, depending upon what value you set the key field to, you could successfully retrieve data by issuing a get, change, or edit table statement that uses the next or prev keywords. If you set the key value to a value before the range, then issue a get next statement, for example, the first record in the range will be retrieved. If you set the key value to a value after the specified range, then issue a get prev statement, the last record in the range will be retrieved.
