(IHASH(arg,n))
R:BASE includes a function that can be used to create an integer value from a text value. The function was designed to create effective integer keys from long text columns. The function, IHASH, converts the entire text value, or just a specified number of characters.
Using this method is more complex that just indexing the LASTNAME column. First you need to add a computed column to your table using the IHASH function on the LASTNAME column to convert its text to integer values. You can modify your table through the Data Designer or the ALTER TABLE command:
ALTER TABLE employee ADD Hash_Lname=(IHASH(lastname,0)) INTEGER
The IHASH function converts the entire name to integer when used with the parameter 0. A different parameter converts the specified number of characters from the name, starting at the first character. For example, the parameter 7 will convert the first 7 characters of the lastname to an integer value. Deciding on the number of characters to convert can be one of the hardest things about using this method. Consider the relationships expressed in the following chart:
Convert FEW characters |
Convert MORE characters |
|
PROS |
Less input required |
Less duplicate values |
CONS |
Greater duplicate values |
More input required |
After adding the computed column to your table, you need to use some programming commands as shown below to query that column. Using the IHASH function directly in a WHERE clause won't use indexes. First set a variable equal to IHASH of the value you're searching for, then use the variable in the WHERE clause. When using an IHASH column for searching, you won't be able to do ad hoc queries from the R:BASE main menu .
SET VAR vname = (IHASH('Smith',0))
SELECT * FROM employee WHERE Hash_Lname = .vname
This method does provide greater flexibility in that you can have users enter anywhere from 1 character to the entire name based on the number of characters you specify in the IHASH function. For example, add a computed column to the table that will IHASH the first four characters of the name. Then, in your program, check the length that the user enters and if it's greater than four characters use an extra condition on your WHERE clause.
DIALOG 'Enter lastname (at least 4 characters):' vname vendkey 1
SET VAR vlen1=(SLEN(.vname)),vname1=(SGET(.VNAME,4,1)),+
vhash=(IHASH(.vname1,4))
IF vlen1 > 4 THEN
CHOOSE vchoice FROM #VALUES FOR (firstname & lastname) +
FROM employee WHERE Hash_Lname=.vhash AND +
(SGET(lastname,.vlen1,1))=.vname
ELSE
CHOOSE vchoice FROM #VALUES FOR (firstname & lastname) +
FROM employee WHERE Hash_Lname=.vhash
ENDIF
A user can enter any number of letters for use with an IHASH computation, but must enter at least as many characters as specified in the IHASH column definition or enter the full name. If the entry less than the specified number of characters and less than the full length of the name, the correct data is not found. For example, with a column defined as (IHASH(lastname,7)), entering "WILL" will not find "WILLIAMS", it will only find "WILL".
The advantages of using a computed column with the IHASH function are that you can turn an inefficient TEXT index into an efficient INTEGER index and you can provide flexibility in searching. Users will have a larger selection of names to choose from and can select the appropriate person from the list. For example, entering WILLIAM will find WILLIAMSON, WILLIAM, and WILLIAMS if you use IHASH(lastname,7).
A disadvantage of this method is that you need to add columns to your database. An extra computed column can slow down data entry. If you are tight on disk space this may not be an option. To determine how much additional disk space you'll need for an IHASH column, take the number of rows in the table and multiply by 4. The answer is the number of bytes of disk space you'll need for the additional column.
