=======================================================================
A TEXT COLUMN SEARCH USING A RANGE OF VALUES
=======================================================================
PRODUCT: R:BASE VERSION : 3.1 Or Higher
=======================================================================
AREA : PROGRAMMING CATEGORY: TEXT SEARCHES DOCUMENT#: 669
=======================================================================
Indexing a TEXT datatype column with a defined length of 4 characters
or less creates what's called a "walkable" index. It's called
"walkable" because the data value does not have to be hashed or
converted to a 4-byte index value. The data value is the same as the
index value. With a "walkable" index, you are not limited to using
the = (equals) operator for doing indexed searches. The BETWEEN
operator can be used to "walk the index" and find all the values
between a specified range. This makes for a fast search of a group of
values.
The R:BASE command file listed below, LOOKUP.RMD, uses an indexed
text column (defined as four characters long) to repeatedly search a
table, making the search more restrictive with each iteration. The
text column used in this program is a computed column using the SGET
function to extract the first four characters of another text column,
LASTNAME. The program initializes two variables to start searching
for everything between "A" and "Z".
The actual searching is done letter by letter, each letter entered by
the user makes the search more restrictive. The user is prompted to
enter one letter at a time. After each letter is entered, the first
15 rows that meet that condition are selected from the table. The
WHERE clause of the SELECT command uses a BETWEEN operator with two
variables for comparison values. The variable vfirst is used as the
lower comparison value. It is originally set to the value 'aaaa' and
then as the user enters letters the values in the variable are
overwritten with the letters entered. The second comparison value
for the BETWEEN is the variable nlast, initially set to 'zzzz'. Just
like the variable first, letters are placed in the variable nlast as
the user enters them. Using the values in the variables first and
nlast, the BETWEEN operator will "walk the key" and quickly find the
data between the specified values. The search can only be restricted
by four letters since the condition is based on an indexed TEXT 4
column. The column can't be defined with a length greater than four
characters. If it is, it is not a walkable key when indexed and the
BETWEEN operator cannot be used to find a range of values.
A menu displays at any time by pressing [Enter] instead of entering
another letter to restrict the search. The menu automatically
displays after four letters have been entered. By using this method
users can quickly and easily generate a menu of names from a large
table.
As an example, let's look for the name "CROCKETT".
1. At the first prompt, enter the letter "C". The variables first
and nlast take on the values "Caaa" and "Czzz" respectively. The
first 15 rows from the table display where the lastname column starts
with "C".
旼컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴�
� Search for nanmes beginning with C �
� CABANA, ALEX �
� CABALLERO, BENJAMIN �
� CABAN, JAIME �
� CABACUNGAN, DOMINGO �
� CABITO, STEVE �
� CABLE, �
� CABLE, �
� CABRERA, CARLOS �
� CACAVIO, GENNARO �
� CACIOPPO, JOSEPH �
� CACKLER, DANNY �
� CADA, JOHN �
� CADDY, IAN �
� CADE, KENNETH �
� Enter letter to search by ([Enter] for menu, [Esc] to exit): �
읕컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴�
2. Next, enter "R" as the second letter. The variables first and nlast
now have the values "CRaa" and "CRzz" respectively. The first 15 rows
display where the lastname begins with "CR".
旼컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴커
� Search for nanmes beginning with CR �
� CRAAN, THOMAS �
� CRABLE, CHERI �
� CRABLE, BRENT �
� CRABTREE, MICHAEL �
� CRABTREE, BRUCE �
� CRABB, J �
� CRACIUM, JOHN �
� CRACHIOLO, CHRISTOPHER �
� CRADDOCK, DAVID �
� CRADDOCK, NICHOLAS �
� CRAFT, ROY �
� CRAFT, M �
� CRAFT, HARRY �
� CRAFT, KEN �
� CRAGER, SCOTT �
� Enter letter to search by ([Enter] for menu, [Esc] to exit): �
읕컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴켸
3. Enter "O" and then "C" again. The menu of names beginning
with "CROC" is displayed and we can choose Davy Crockett from the
menu.
旼컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴커
� Search for nanmes beginning with CRO �
� CROCKER, DENNIS �
� CROCKETT, JOHN �
� CROCKER, G �
� CROCKER, ROBERT �
� CROCKETT, DAVY �
� CROCKETT, DENNIS �
� CROCKER, STEPHEN �
� CROCKETT, ROSEMARY �
� CROCKER, DOUGLAS �
� CROCKETT, BARBARA �
� CROISSANT, JUDY �
� CROKER, LINDA �
� CROMBIE, ROB �
� CROMIE, SCOTT �
� CROMMETT, JOYA �
� Enter letter to search by ([Enter] for menu, [Esc] to exit): �
읕컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴켸
旼컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴커
� Search for nanmes beginning with CROC �
� CROCKER, DENNIS �袴袴袴袴袴袴袴袴袴袴袴袴� �
� CROCKETT, JOHN � DENNIS CROCKER � �
� CROCKER, G � DOUGLAS CROCKER � �
� CROCKER, ROBERT � G CROCKER � �
� CROCKETT, DAVY � ROBERT CROCKER � �
� CROCKETT, DENNIS � STEPHEN CROCKER � �
� CROCKER, STEPHEN � BARBARA CROCKETT � �
� CROCKETT, ROSEMARY �=>DAVY CROCKETT<========� �
� CROCKER, DOUGLAS � DENNIS CROCKETT � �
� CROCKETT, BARBARA 훤袴袴袴袴袴袴袴袴袴袴袴暠 �
� Enter letter to search by ([Enter] for menu, [Esc] to exit): �
읕컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴켸
This example searches for names. It can easily be modified to search
for other data. The only requirement is that the column used to
restrict the search must be defined as TEXT 4 and indexed. It could
be a product number or code column; it doesn't need to use the SGET
function.
*(LOOKUP.RMD)
CLS
SET MESSAGES OFF
SET ERROR MESSAGES OFF
-- 1. Initialize variables. If the search is not restricted,
-- everything between A and Z is found
SET VAR v1 INT=1, acode TEXT, first TEXT='aaaa', nlast TEXT='zzzz'
-- 2. Repeat the search process 4 times. Note that v1 starts at 1
-- not 0, and is why the WHILE loop uses <= 5 for 4 iterations.
WHILE v1 <= 5 THEN
-- 3. Automatically display the menu after entering the 4th letter
-- for qualifying the search
IF V1 = 5 THEN
CHOOSE vname FROM #VALUES FOR (firstname&lastname) FROM customer +
WHERE first_4 BETWEEN .first AND .nlast ORDER BY +
lastname,firstname AT CENTER,CENTER FOOTING
BREAK
ENDIF
-- 4. The syntax, FILLIN varname=0, accepts a single character input
-- only. This is used to restrict the search letter by letter.
FILLIN acode=0 USING 'Enter letter to search by ([Enter] for menu,+
[Esc] to exit): '
IF acode='[Esc]' THEN
RETURN
ENDIF
-- 5. display the menu anytime the user presses [Enter] instead of
-- another letter.
IF acode = '[Enter]' THEN
CHOOSE vname FROM #VALUES FOR (firstname&lastname) FROM customer +
WHERE first_4 BETWEEN .first AND .nlast ORDER BY +
lastname,firstname AT CENTER,CENTER FOOTING
BREAK
ENDIF
-- 6. Creates the new search criteria. Because v1 is used as an
-- argument in the SPUT command, it is initalized to 1 at the start
-- of the program (not 0).
SET VAR first=(SPUT(.first,.acode,.v1)),nlast=(SPUT(.nlast,.acode,.v1))
CLS
-- 7. Create a message to let users know what letters have been
-- chosen
SET VAR vbeg=(SGET(.first,.v1,1)), vmsg =('Searching for names +
beginning with' & LUC(.vbeg))
WRITE .vmsg
SET HEADING OFF
-- 8. Selects the first 15 rows from the table based on the search
-- criteria, and increments the counter.
SELECT (lastname+','&firstname)=40 FROM customer WHERE first_4 +
BETWEEN .first AND .nlast AND LIMIT = 15
SET VAR v1 = (.v1 + 1)
-- 9. Error trapping code if no data is found.
IF SQLCODE = 100 and v1 <= 5 THEN
SET VAR vmsg = ('No data exists for names beginning with'&LUC(.vbeg)), +
vmsg1=(CTR(.vmsg,78)+char(255) )
WRITE .vmsg1 AT 24,1 GRAY ON RED
PAUSE 2
CLS
SET VAR first = 'aaaa', nlast = 'zzzz', v1 = 1
ENDIF
ENDWHILE
RETURN