Indexing on a Character and Numeric Field Together (115426)

MORE INFORMATION

NOTE: This can also be accomplished by using the SQL SELECT command with its ORDER clause.

The following table is in physical order. The CHAR field is a character field with a length of 10, and the NUM field is a numeric field with a width of 5.

   CHAR                         NUM
   ---------------------------------

   Apple                        -299
   Apple                        -298
   Zebra                        3900
   Yankee                         -9
   Octopus                       237
   Frank                        -222
   Yankee                          1
   Zebra                       -4000
   Apple                        2200
   Apple  a                     2200
				

The difficulty here is how to index on a string negative number. In a character-based ascending index scheme, -004 doesn't come before -003. FoxPro orders the data using the ASCII equivalents. To have the numbers appear in ascending order, the numeric value must first be added to the largest possible value for that field (99999 in this example) and then converted to a string. For example, the following INDEX command creates a compound index named COMBO that is in ascending order when the numeric field is five spaces wide:

   INDEX ON Char+STR(99999+Num) TAG Combo
				

The following table contains the same data as above. However, it is now in ascending order based on the COMBO index:

   CHAR                         NUM
   ---------------------------------

   Apple                        -299
   Apple                        -298
   Apple                        2200
   Apple  a                     2200
   Frank                        -222
   Octopus                       237
   Yankee                         -9
   Yankee                          1
   Zebra                       -4000
   Zebra                        3900