This shows you the differences between two versions of the page.
|
admin:indexing:powersearch:sql [2011/01/24 04:20] els |
admin:indexing:powersearch:sql [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Omnidex Indexing ====== | ||
| - | |||
| - | ===== PowerSearch ===== | ||
| - | |||
| - | [[admin:indexing:powersearch:home|Overview]] | | ||
| - | [[admin:indexing:powersearch:example|Example]] | | ||
| - | **[[admin:indexing:powersearch:sql|SQL]]** | | ||
| - | [[admin:indexing:powersearch:optimization|Optimization]] | | ||
| - | [[admin:indexing:powersearch:tips|Tips]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | |||
| - | |||
| - | ==== The SQL Behind PowerSearch ==== | ||
| - | |||
| - | In most scenarios, the key to implementing PowerSearch is the $CONTAINS function in Omnidex SQL. This function provides access to most of the search capabilities shown on the previous page. | ||
| - | |||
| - | === The $CONTAINS Function === | ||
| - | |||
| - | The $CONTAINS function is used in the WHERE clause of a SELECT statement to provide criteria accompanied by special options. In traditional SQL statements, criteria is specified using a syntax of: | ||
| - | |||
| - | <code sql> | ||
| - | select count(*) | ||
| - | from LIST | ||
| - | where FNAME = 'Bill'; | ||
| - | </code> | ||
| - | |||
| - | The $CONTAINS function allows this same statement to be reworded as follows: | ||
| - | |||
| - | <code sql> | ||
| - | select count(*) | ||
| - | from LIST | ||
| - | where $contains(FNAME, 'Bill'); | ||
| - | </code> | ||
| - | |||
| - | The real advantage of the $CONTAINS function lies in the options that can be added. The $CONTAINS function is discussed in depth in the [[dev:sql:functions:contains:home|documentation]] on the function. Meanwhile, the following examples show how the options were used to provide each of the capabilities shown on the previous page. | ||
| - | |||
| - | |||
| - | === First Name === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(FNAME, 'Bill', | ||
| - | 'SYNONYMS=ALL_GIVEN_NAMES') ... | ||
| - | </code> | ||
| - | |||
| - | === Last Name === | ||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(LNAME, 'Meyers', | ||
| - | 'PHONETIC') ... | ||
| - | </code> | ||
| - | |||
| - | === Address === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(ADDRESS1, '825 Fifth Avenue', | ||
| - | 'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') ... | ||
| - | </code> | ||
| - | === City === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(CITY, 'NY', | ||
| - | 'SYNONYMS=CITY_ABBR,MISSPELLINGS') ... | ||
| - | </code> | ||
| - | |||
| - | === Zip Code === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where ZIP in | ||
| - | (select ZIP | ||
| - | from ZIPCODES | ||
| - | where $distance((select LATITUDE, | ||
| - | LONGITUDE | ||
| - | from ZIPCODES | ||
| - | where ZIP = '10022'), | ||
| - | LATITUDE, | ||
| - | LONGITUDE) <= 5) ... | ||
| - | </code> | ||
| - | |||
| - | === Phone Area Code === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(PHONE1_AREA, '917', | ||
| - | 'SYNONYMS=AREACODES') ... | ||
| - | </code> | ||
| - | === Phone Prefix === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(PHONE1_PREFIX, '755', | ||
| - | 'MISSPELLINGS MIN_SCORE=70') ... | ||
| - | </code> | ||
| - | === Phone Suffix === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(PHONE1_SUFFIX, '4686', | ||
| - | 'MISSPELLINGS MIN_SCORE=70') ... | ||
| - | </code> | ||
| - | |||
| - | === Email === | ||
| - | |||
| - | <code sql> | ||
| - | select ... | ||
| - | from ... | ||
| - | where $contains(EMAIL, 'wmeyers@med.cornell.edu', | ||
| - | 'MISSPELLINGS MIN_SCORE=80') ... | ||
| - | </code> | ||
| - | |||
| - | === Final SQL Statement === | ||
| - | |||
| - | When combined, the final SQL statement has many $CONTAINS functions which combine to provide the full power of PowerSearch: | ||
| - | |||
| - | <code sql> | ||
| - | select count(*) | ||
| - | from LIST | ||
| - | where $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and | ||
| - | $contains(LNAME, 'Meyers', 'PHONETIC') and | ||
| - | $contains(ADDRESS1, '825 Fifth Avenue', | ||
| - | 'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and | ||
| - | $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and | ||
| - | LIST.STATE = 'NY' and | ||
| - | ZIP in | ||
| - | (select ZIP | ||
| - | from ZIPCODES | ||
| - | where $distance((select LATITUDE, | ||
| - | LONGITUDE | ||
| - | from ZIPCODES | ||
| - | where ZIP = '10022'), | ||
| - | LATITUDE, | ||
| - | LONGITUDE) <= 5) and | ||
| - | $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') and | ||
| - | $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') and | ||
| - | $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70') and | ||
| - | $contains(EMAIL, 'wmeyers@med.cornell.edu', 'MISSPELLINGS MIN_SCORE=80'); | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[admin:indexing:powersearch:example|Prev]]** | | ||
| - | **[[admin:indexing:powersearch:optimization|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||