How to do a descending sort

Charlie Meyers of Schwartz Brothers asked us how to set up a B-tree with a descending key. Let's assume attribute 5 of our data records contain values from -999 to 999, inclusive. If we build a B-tree called TEST, and BTPKEY contains

CASE ROOT = "TEST"
  KEY = ITEM<5>:nul:ID
  

then the data records will be (undesirably) sorted by the B-tree to reflect the following order of sample data values:

  -1
  -9
 -99
-999
   0
   1
   9
  99
 999
 

We can fix the problem with the above list by using right justification to force a numeric sort, as described in Branches #1. For example,

CASE ROOT = "TEST"
  KEY = (ITEM<5>"R#10"):nul:ID
  

effectively sorts to:

   0
   1
   9
  -1
  -9
  99
 -99
 999
-999

The above list shows that right justification alone is fine if all the data is positive, but negative numbers still aren't sorted right because of the collating order of ASCII blanks and minus signs. (Remember, B-TREE-P is comparing keys produced by KEY = expression in BTPKEY, which is never completely numeric because of the insertion of nul characters in expression.) Negatives can be correctly handled with an expression like

CASE ROOT = "TEST"
 KEY=((ITEM<5>+999)"R#10"):nul:ID
 

which causes the desired sort:

-999
 -99
  -9
  -1
   0
   1
   9
  99
 999
 

In other words, adding the absolute value of the smallest possible integer to each data value effectively shifts the data range of -999 to 999 into the all-positive range of 0 to 1998, thereby avoiding minus signs and allowing the right justification to create a numeric sort. By inverting the logical data values with an expression like

CASE ROOT = "TEST"
 KEY=((999-ITEM<5>)"R#10"):nul:ID
 

we effectively create a descending numeric sort:

 999
  99
   9
   1
   0
  -1
  -9
 -99
-999

Just remember to use large enough constants to offset negative data values, and use justification widths wide enough to avoid truncating any digits.