TITLE:Back-to-Basics: ROOT and KEY
ISSUE:Multi-value Solutions Feb '98
AUTHOR:Nathan Rector
COMPANY:Natec Systems
EMAIL:nater@northcoast.com
HTTP:www.northcoast.com/~nater/
With the introduction of the System level B-Tree in Advanced Pick and D3, they also include two new commands in BASIC that allow a program to make use of the B-Tree without having to resort to ACCESS.
These two BASIC command are ROOT and KEY. They work seemlessly as one just like an OPEN and READ statement does. ROOT is the equivalent of the OPEN statement. KEY is the equivalent of a READ.
The ROOT statement is used to open a specific index by supplying the file name and the index key the program wishes to look through.
syntax:
ROOT filename,keyname TO root.var THEN/ELSE
FILENAME = "CUSTOMER"
KEYNAME = "A1"
ROOT FILENAME,KEYNAME TO ROOT ELSE
CRT "Key ": KEYNAME :" does not exist in ": FILENAME
STOP
END
The keyname is the same A-correlative that was used in the CREATE-INDEX command. However, this causes a small limitation to keep in mind when using the ROOT command. If the index changes at all and the keyname is hard coded into the program, the program has to be changed.
The KEY command is used to move through the index opened by ROOT.
Syntax:
KEY(operator,root.var,index.key,item.id{,vc.expression}) THEN/ELSE
There are several operators that can be used with the KEY command:
n - Next Key. Compares left-to-right against the "index.key" and return the first "item.id" that index matches. If no matches are found it returns the next highest "index.key" and "item.id" found. If there is more than one "item.id" found for the "index.key" then it returns the next "item.id".
p - Previous Key. Works the same as ‘n’ but in reverse order.
r - Returns only something in "item.id" when an exact match is found. This returns only a single entry.
x - Works like the ‘r’, but returns all the items that match. It returns all the items in "item.id" as a dynamic array.
Here is a simple example of using the ‘x’ operator:
FILENAME = "CUSTOMER"
KEYNAME = "A1"
ROOT FILENAME,KEYNAME TO ROOT.VAR ELSE
CRT KEYNAME :" does not exist in ": FILENAME
STOP
END
*
KEY.TO.MATCH = "MATT"
KEY("X",ROOT.VAR,KEY.TO.MATCH,ITEM.ID) THEN
CRT DCOUNT(ITEM.ID,AM) :" items found"
END ELSE
CRT "NO ITEMS PRESENT"
END
STOP
END
Here is a simple example of using the ‘n’ and ‘p’ operator. One thing that can be confusing when working with ‘n’ and ‘p’ operators is how the INDEX.KEY var increments to the next value in the list. For example, if you input MATT and the next item in line is SCOTT, then after the INDEX.KEY var has been processed through the KEY command, it has a value of SCOTT, even though the ITEM.ID var is the item id for MATT.
FILENAME = "CUSTOMER"
KEYNAME = "A1"
ROOT FILENAME,KEYNAME TO ROOT.VAR ELSE
CRT KEYNAME :" does not exist in ": FILENAME
STOP
END
*
KEY.TO.MATCH = "MATT"
OP = "N"
*
LOOP
CRT "Match: ": KEY.TO.MATCH :
KEY(OP,ROOT.VAR,KEY.TO.MATCH,ITEM.ID) THEN
CRT " - Item Id: ": ITEM.ID
END ELSE
IF OP = "P" THEN
CRT "Beginning of list"
END ELSE
CRT "End of list"
END
END
*
CRT "Option: (N)ext key, (P)revious Key, e(X)it":
INPUT OP
UNTIL OP = "X" DO
REPEAT
STOP
END
:TEST
Match: MATT - Item Id: 234
Option: (N)ext key, (P)revious Key, e(X)it N
Match: SCOTT - Item Id: 848
Option: (N)ext key, (P)revious Key, e(X)it P
Match: MATT - Item Id: 234
Option: (N)ext key, (P)revious Key, e(X)it X
:
The ROOT and KEY commands are nice to use when working with complex index keys that ACCESS statements can not use. They are also handy when you are trying to create a lookup program that needs the ability to scroll back and forth through selections.