=======================================================================
     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