Case-Insensitive and Descending Segments
You can designate individual segments of indexes as case-insensitive and/or descending. Case-insensitive segments, which are not recommended with SQL databases, are particularly valuable when dealing with material such as names that may contain variations in capitalization unknown to users searching for records. In indexes containing case-insensitive segments, capitalization in the column in question does not affect the sequencing of records. When this attribute is not used, capital letters are sequenced immediately ahead of lowercase letters. The sequence is AaBbCcDd, and so on.
Index segments designated as descending (the default is ascending) can also be very useful, but they impose certain requirements on programming. For example, when a find ... gt statement is executed on an index containing descending segments, the gt operates as a lt on the descending segments. When a starting value is moved to the record buffer to set a starting point for a loop containing such a command, the starting values for these segments should be at the bottom of the desired range, rather than at the top as for default (ascending) indexes.
For instance, if a report of all accounts with amounts over 30 days overdue is desired, and the index on the number of days overdue is (properly) descending, the effective approach would be to start with a clear buffer and a find ... ge statement.
If the index consists of, say, the number of days overdue followed by customer name, the ge would have the effect of a le on the first segment (number of days), and the last record by the index (greatest number of days overdue) would be found. On the next segment, customer name, the ge would find the first customer alphabetically, and customers would be listed alphabetically for each number of days overdue. The report would continue with a loop containing find ... gt until the value 30 is encountered in days overdue, at which point the report would terminate.
Case-insensitive index segments can reduce the usability of find optimization, in which reports can output index values instead of actual record values. The values stored in such segments are all upper case, so if the field values must be output as actually capitalized in the database, the record buffer will have to be loaded to access the actual values. Find optimization works by making it unnecessary to load the record buffer. Where output in solid capital letters is acceptable, this may not pose any problem.
While SQL index text segments can be defined to be case-insensitive (uppercased), this technique is not advised when using SQL tables. See SQL Indexes for more about using DataFlex with SQL table indexes.