Page History
...
| Parameter | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| Srch_strng | Must end with a field mark. The basic unit of srch_strng is a search column. If you want to search by more than one criterion, include additional search columns in srch_strng. Each search column is delimited from another by a field mark (@FM), and multiple search columns imply an And relation (conditions must be satisfied for all search columns before any key is returned). The And relation may be overridden for individual search values by prefixing each with a semicolon (;). Searchcolumn structure Syntax searchcolumn = IndexedColumn:@VM:data1 [:@VM:data2 ...]:@FM Made up from the name of an indexed column and the search values to be located within that column. A Btree index must have been applied to the specified row column, or an error is generated. Within the search column, search values are delimited from the indexed column name and each other by a value mark (@VM). Within the search column, search values are located based on an Or relation: a row key is returned when any of the values is found within the specified column. The implied Or relation for the @VM-delimited search values may be forced to an And relation by prefixing any desired search value with an ampersand (&). This situation presumes a multi-valued column, because every And relation means that two values must be found before a hit is registered. | ||||||||
| Table | Pass the name of the table to be searched. | ||||||||
| Dictvar | Pass the file handle for the dictionary of the specified table. | ||||||||
| Keys | Returns row keys for all rows that satisfy the criteria in srch_strng. Multiple keys are delimited with value marks. | ||||||||
| Option | Three values for option are possible:
| ||||||||
| Flag | Error codes are returned in flag. After execution, keys contains a list of keys matching the search criteria. flag returns one of several possible values, depending on the success of the search process.
|
...
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:
| Code Block |
|---|
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:
| Code Block |
|---|
srch_strng = "CITY":@VM:">=S":@FM: "CITY" : @VM @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:
| Code Block |
|---|
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:
...
| Code Block |
|---|
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 |
...
