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.