Page History
...
Btree.Extract also allows you to provide your own routines for preprocessing search data, as well as for your own search algorithm. Refer to "User Index Facility" and "User Index Extension to Btree.Extract" topics for more information.
...
Note: The Btree.Extract routine will update Btree indices prior to the extract if the environment parameter for ENV_BTREE_FLUSH_ON$ is set to true. The Database Manager's Environment Settings window contains a checkbox to turn the update flag on.
See also
BTree.Read, Extract_SI_Keys, IXLOOKUP event, Update_Index, Collect.IXVals()
Example
| Code Block |
|---|
/* The following code fragment opens the dictionary to a table, then sets up a search that looks for all records having either data1 or data2 as values in the specified column. The routine returns this list of keys to the calling procedure. */ Declare Subroutine Btree.Extract table = "CAR_PARTS" Open "DICT ":table To @DICT Else RetVal = Set_FSError() Return End Column = "PART_NAME" data1 = "WHEEL, STANDARD" data2 = "TIRE, BIAS PLY" search_criteria = column:@VM:data1:@VM:data2:@FM keylist = "" option = "" flag = "" Btree.Extract(search_criteria, table, @DICT, keylist, option, flag) Return keylist |
Searching by NOT
The search values in srch_strng are substrings, and as such, you can modify the search relations using substring search characters. For example,
| Code Block |
|---|
srch_strng = "COMPANY_NAME":@VM:"#TRUST INSURANCE":@FM
will find all values of the indexed column COMPANY_NAME that are not "TRUST INSURANCE".
Searching by BETWEEN
Btree.Extract supports a special search operator, between ("~" (tilde)).
The operator between differs from range in that it is not inclusive.
For example, the following search string will return row keys for all rows having ZIP codes between,
but not including, 98100 and 98111:
srch_strng = "ZIP":@VM:"98100~98111":@FM |
...
Searching by AND
As noted above, multiple search criteria are linked with an implicit And. The OpenList filter sub-statement With STATE = "CT" And CITY = "Stamford" is performed in Btree.Extract by the code:
| Code Block |
|---|
"STATE":@VM:"CT":@FM:"CITY":@VM:"Stamford":@FM |
...
Searching by OR
To change the implicit And to an Or, insert a semicolon (;) before each search value in the second (and subsequent) search column(s). The statement With STATE = "CT" Or CITY = "Stamford" is performed in Btree.Extract by the code:
| Code Block |
|---|
"STATE":@VM:"CT":@FM:"CITY":@VM:";Stamford":@FM Starting, Ending, and Containing The greater than sign (>) and greater than or equal to signs (>=) are valid search string arguments. To extract keys of all rows with City starting with S, code the following search string: srch_strng = "CITY":@VM:">=S":@FM: "CITY" : @VM : "<T" : @FM The "ending with" indicator (]) is also a valid search string argument. To extract keys of all rows with City ending with P, code the following search string: srch_strng = "CITY":@VM:"P]":@FM The "containing" indicators ([]) also are valid search string arguments, allowing for a substring search. To extract keys of all rows with City containing the letter P, code the following search string: srch_strng = "CITY":@VM:"[P]":@FM |
...
User Index Extension to Btree.Extract
...
| Code Block |
|---|
Subroutine Parser(search_val, start_value, user_index_flag,|comp_mod) /* This subroutine establishes three independent subroutines, each with a different purpose within the user index facility of Btree.Extract. */ /* This code is an example of a user index parser routine to intercept Soundex lookups (it looks for values starting with "$"). */ Declare Subroutine Soundex ;* code supplied below Equate TRUE To 1 Equate TRIGGER To "$" Equate COMP_MOD To "COMPARE" /* The following code examines the index lookup value and determines whether it is a Soundex lookup. If not, the search value is passed through to Btree.Extract as normal. */ If search_val[1,1] = TRIGGER Then search_val[1,1] = "" ;* delete the trigger character Soundex(search_val) ;* convert to Soundex value user_index_flag = TRUE;* use custom compare in ascending start_value = search_val[1,1];* start at first letter COMP_MOD = COMP_MOD ; * specify the compare module End Return |
...
Example 2 (Compare)
| Code Block |
|---|
Subroutine COMPARE(candidate, search_val, flag) Declare Subroutine Soundex Equate HIT_TRUE$ To 1 Equate HIT_FALSE$ To 0 Equate QUIT_SEARCH$ To 2 If candidate[1,1] GT search_val[1,1] Then flag = QUIT_SEARCH$ ;* end search if first char not same End Else Soundex(candidate) ;* convert CANDIDATE to Soundex If search_val Eq CANDIDATE Then flag = HIT_TRUE$ End Else flag = HIT_FALSE$ End End Return |
...
Example 3 (Soundex)
| Code Block |
|---|
Subroutine Soundex(soundex_value)
/* This is code for a subroutine that returns the Soundex equivalent of a single word.
First, establish the numeric code equivalents for all letters of the alphabet-vowels,
plus 'w', 'y', and 'h' are ignored. */
Equate SOUND.CODES To "01230120022455012623010202"
Equate PUNCTUATION To ".,/`';][-=<>?:~}{+_)(*&^%$#@!\|":CHAR(34)
Equate NUMERALS To "1234567890"
Equate NULL$ To ""
/* Be sure there is only one word, no punctuation and no lower case or numeric characters. */
text = Trim(soundex_value)
text = Field(text, " ", 1)
Convert PUNCTUATION To null In text
Convert NUMERALS To null In text
Convert @LOWER.CASE To @UPPER.CASE In text
/* In accordance with the Soundex algorithm, start with the 2nd character. */
first_char = text[1,1]
text = text[2,999]
text_length = Len(text)
soundex_value = first_char
previous_char = NULL$
FOR loop_count=1 To text_length While Len(soundex_value) < 4
* strip off next character
next_char = text[loop_count, 1]
If next_char NE previous_char Then
Convert @UPPER.CASE To SOUND_CODES In next_char
If next_char NE 0 Then
soundex_value := next_char
previous_char = next_char
End
End
Next loop_count
/* Format is four characters in length, zero-padded at right if necessary. */
soundex_value = Fmt(soundex_value, "L(0)#4")
Return |
