This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:powersearch:sql [2011/01/24 04:17] els |
admin:indexing:powersearch:sql [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 9: | Line 11: | ||
| **[[admin:indexing:powersearch:sql|SQL]]** | | **[[admin:indexing:powersearch:sql|SQL]]** | | ||
| [[admin:indexing:powersearch:optimization|Optimization]] | | [[admin:indexing:powersearch:optimization|Optimization]] | | ||
| + | [[admin:indexing:powersearch:custom|Customization]] | | ||
| [[admin:indexing:powersearch:tips|Tips]] | [[admin:indexing:powersearch:tips|Tips]] | ||
| Line 17: | Line 20: | ||
| ==== The SQL Behind PowerSearch ==== | ==== 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 POWERSEARCH Option === |
| - | === The $CONTAINS Function === | + | In most scenarios, the key to implementing PowerSearch is to simply use the POWERSEARCH option on a SELECT statement. Alternatively, the SET POWERSEARCH ON command can be used to automatically activate PowerSearch for all queries on that connection. |
| - | 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> |
| + | > select name from individuals where name = 'William Cooke' with powersearch; | ||
| - | <code sql> | + | NAME |
| - | select count(*) | + | -------------------------------------------------- |
| - | from LIST | + | BILLIE E COOK |
| - | where FNAME = 'Bill'; | + | MR. WILL COOK |
| </code> | </code> | ||
| - | The $CONTAINS function allows this same statement to be reworded as follows: | + | === The USAGE Clause === |
| - | <code sql> | + | The POWERSEARCH option works hand-in-hand with the data stored in the Omnidex Environment File. To use POWERSEARCH, administrators add a context to columns telling Omnidex what they contain. For example, a column named NAME may have a character datatype, but that alone is not enough to tell Omnidex what the column contains. Moreover, Omnidex cannot look to the column name, as there are many different conventions for naming columns. Administrators add a USAGE clause that identifies the contents as given names, as shown in this example: |
| - | 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. | + | <code> |
| - | + | create table "INDIVIDUALS" | |
| - | + | physical "dat\individuals.dat" | |
| - | === First Name === | + | ( |
| - | + | "INDIVIDUAL" CHARACTER(12) omnidex, | |
| - | <code sql> | + | "HOUSEHOLD" CHARACTER(12) omnidex , |
| - | select ... | + | "NAME" CHARACTER(50) quicktext usage "NAME", |
| - | from ... | + | "GENDER" CHARACTER(1) omnidex bitmap, |
| - | where $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') ... | + | "BIRTHDATE" ANSI DATE omnidex, |
| + | "PHONE" CHARACTER(14) omnidex usage "PHONE", | ||
| + | "EMAIL" CHARACTER(60) quicktext usage "EMAIL", | ||
| + | constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| + | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
| + | constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | ) | ||
| + | in "simple.xml"; | ||
| </code> | </code> | ||
| - | === Last Name === | + | Omnidex provides many different usages to meet the general needs of most applications; however, administrators can add custom usages to meet the specific needs of their application. The general usages provided by Omnidex include: |
| - | <code sql> | + | |
| - | select ... | + | |
| - | from ... | + | |
| - | where $contains(LNAME, 'Meyers', 'PHONETIC') ... | + | |
| - | </code> | + | |
| - | === Address === | + | * SALUTATION |
| + | * FIRST_NAME | ||
| + | * MIDDLE_NAME | ||
| + | * LAST_NAME | ||
| + | * NAME | ||
| + | * COMPANY_NAME | ||
| + | * STREET_NUMBER | ||
| + | * STREET_DIRECTION | ||
| + | * STREET_NAME | ||
| + | * STREET_TYPE | ||
| + | * UNIT_TYPE | ||
| + | * UNIT_NUMBER | ||
| + | * CITY | ||
| + | * STATE | ||
| + | * COUNTRY | ||
| + | * ZIPCODE | ||
| + | * STREET_ADDRESS | ||
| + | * CITY_STATE_POSTAL | ||
| + | * ADDRESS | ||
| + | * PHONE_AREACODE | ||
| + | * PHONE_PREFIX | ||
| + | * PHONE_SUFFIX | ||
| + | * PHONE | ||
| + | |||
| + | * ... plus, you can add any custom usages needed for your application. | ||
| - | <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> | ||
| Line 157: | Line 97: | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| + | |||