Building and Executing Queries
Scenario 1: A Simple Query Across all Fields
The sample program java\sample\QrySimple.java performs a simple search across the full text of a record. You can review the code if required (Java) or run it. Add the name of the table and search text. For example, search the person table created for “jasoz fitgerlad”.
cd $PATTERNS_HOME/
java -cp java\lib\TIB_tps_java_interface.jar java\sample\QrySimple.java person "jasoz fitgerlad"
You can see the following results.
key score record text
1395 0.662 Fitzgereld,Jasom,151507584,
200 NClasson Str,St.Paul,MN,55101
1394 0.662 Fitzgerald,Jason,151607584,
2000 N Classen Blvd,Saint Paul,MN,55101
1396 0.572 Fitzgreld,Jasom,151507584,
200 N Classon Str,St.Paul,MN,55101
1397 0.500 Fitzgreld,Jassy,151507584,
200 N Classon Str,St.Paul,MN,55101
7511 0.492 Hull,Jason,570766446,40 Ridge
Dr Apt 101,Dallas,TX,75234
Results similar to the input are found, even when the text is scrambled.
This very simple search doesn’t distinguish between fields. Searching for “paul fitgerlad” yields the following results.
key score record text
1395 0.648 Fitzgereld,Jasom,151507584,
200 N Classon Str,St. Paul,MN,55101,RGQY-8946-WPII
5736 0.598 Drooking,Paul,,1 Ford Pl # 2e,
Columbus,GA
1642 0.549 Litzinger,Paul,570000884,3808 N Tamiami Trl,
San Francisco,CA
40 0.543 Sorenson,Paul,942323408,4 Vilalge Dr,
Houston,TX,77210
312 0.543 Bearden,Paul,,860 Ridge Lake Blvd,
Rochester,MN,55905
Note that "paul" can appear in any field.
Scenario 2: Using a Thesaurus
The sample program java/sample/src/QryThes.java also performs a search across all fields, and adds the use of a thesaurus to handle nicknames. You can review the code if required (Java), or run it. Give it the name of the table, the name of the thesaurus, and search text.
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryThes.java person nicknames "peggy sooder"
You can see the following results.
key score record text
111 0.754 Souders,Margaret,361363143,621 Nw 53rd St,Naperville,IL,60565
5707 0.642 Smith,Ryder,,832 Marguerite Mine Rd,Knoxville,IA,50138
6377 0.624 Shoults,Margaret,135155146,104 Whippoorwill
Dr,Mobile,AL,36619
4569 0.622 Wolf,Margaret,812653366,7420 Grace Dr,Scottsdale,AZ,85260
8550 0.610 Sun,Peggy,,21093 Vinton Lane,Overland Park,KS,66212
For comparison, you can run QrySimple.java on "peggy sooder" to see results without the thesaurus.
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QrySimple.java person "peggy sooder"
You can see the following results.
key score record text
8550 0.610 Sun,Peggy,,21093 Vinton Lane,Overland Park,KS,66212
1307 0.603 Beam,Peggy,,11 W Mohawk Dr,Oxford,MS,38655
9741 0.496 Sonderman,Jeff,,Po Box 2900,Miami,FL,33166
6326 0.494 Hehr,Peggy,297933019,5300 Kings Island Dr,North Bend,WA,98045
3344 0.453 Goodpaster,Don,,Po Box 687,Tulsa,OK,74134
The results using the thesaurus are precise.
Scenario 3: Targeting to Specific Fields
In many use cases it is required that the specific portions of a query target different fields of the table.
Terminology:
| • | A query is the top-level search submitted to the Patterns server. |
| • | A querylet or sub-query is a piece of a query. |
| • | A combiner is formed using querylets. Combiners often have options (such as weighting). |
A query can be either a querylet or a (most commonly) combiner.
For example, you want to find a record belonging to a David Brown who lives on Bath Street.
The sample program java/sample/src/QryTargeted.java performs a field-targeted search. You can review the code if required (Java) or run it. Give it the name of the table, the first name, the last name, and the street.
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryTargeted.java person dvid brwn bath
You can see the following results.
key score record text
6232 0.95 Brown,David,695674330,3161 Bath Pike,Chesapeake,VA,23320
8456 0.70 Brown,David,,931 14th St # 1310,Lakeland,FL,33813
1539 0.63 Brown,David,445772053,4313 Grainwood Cir Ne,Seattle,WA,98124
9973 0.59 Erwin,David,,1232 7th Ave,Kirkland,WA,98083
5616 0.58 Winters,David,323046791,508 Dartmouth Crossing Dr,Palm City,FL,34990
Notice multiple David Browns are available, all of them found despite the multiple misspellings of his name. However, only one of these David Brown’s lives at an address containing “bath”. This record has a substantially higher score..
The sample program java/sample/src/QryTargeted.java makes use of Patterns' “AND” operator. This may remind you of the “AND” operator from other query languages. But unlike the hard-coded “AND” relation that forcibly includes multiple terms, the TIBCO Patterns - Search “AND” operator behaves in a “soft” manner. It is a combiner that computes a weighted average.
Scenario 4: Using a Predicate to Filter Records
At times it is necessary to filter certain kinds of records out of the result set, based upon the content of a certain field.
For example, suppose you wish to find all the people with the last name of ‘Browne’ who live in the state of Oregon (‘OR’).
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryPredFilter.java person browne OR
You can see the following results.
key score record text
9316 1.00 Browne,Rennel,,4975 Lacross Rd Ste 20,Eugene,OR,97401
3286 0.91 Brown,T,757694401,706 Mission St Fl 8,Milwaukie,OR,97222
1280 0.76 La Brie,Bill,983987453,Technology Division,Terrebonne,OR,97760
1116 0.73 Bristow,Donald,473762663,12030 Pasteur
Dr Apt 413,Corvallis,OR,97333
3661 0.72 Rowley,Roger,,5733 Central Ave,Portland,OR,97233
Note how the predicate filter works in concert with inexact matching. The string specified in the predicate filter must match the “state” field exactly. At the same time, the usual inexact matching is applied to the querylet: “Brown” records are returned (only Browns living in California) despite the misspelling of the name.
TIBCO Patterns - Search offers a rich set of options for constructing complex predicate expressions. See Predicates for more information.
Scenario 5: Alternate Conditions
Sometimes it is necessary to find that match one at least one of several conditions. The sample program java/sample/src/QryOR.java does this. Using TIBCO Patterns’ OR operator, it searches for records with a particular last name or a particular city. For example, searching for last name “Xu” or city “Russell”.
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryOr.java person xu russell
You can see the following results.
key score record text
1186 1.00 Soltys,John,715187409,1200 Lower River Rd Nw,Russell,KS,67665
6688 1.00 Xu,Brian,585404059,2662 Holcomb Bridge Rd,Mentor,OH,44060
7850 1.00 Suttin,Barry,856722824,334 Tasf,Russell,KY,41169
8765 1.00 Xu,James,,1732 Elmwood Dr,Charlotte,NC,28277
1275 0.56 Ellister,Mark,,2230 E Imperial Hwy,Roswell,GA,30076
Scenario 6: Targeted Queries with Cross-field Matching
Different parts of personal names are frequently entered incorrectly in fields. Some last names sound like first names, or vice versa. Sometimes multiple surnames are entered in different name fields. Targeted queries are useful, but with cases like name fields, you might want to target specific fields, while simultaneously permitting selective cross-field matching.
The sample program java/sample/src/QryCognate.java does this. Using TIBCO Patterns’ Cognate feature, it searches for specifically the last-name and first-name fields, while accounting for data that has cross between those two fields.
cd $PATTERNS_HOME java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryCognate.java person Sam Sang 1.0
You can see the following results.
key score record text
1636 0.97 Sangsom,Sam,,2015 Manhattan Beach Blvd,San Diego,CA,92154
6338 0.75 Nam,Sang,,5445 Dtc Pkwy Ste 600,Sunnyvale,CA,94088
903 0.67 Sarmad,Sam,300461920,12479 Research Pkwy,Salt Lake City,UT,84112
1053 0.66 Sabesan,Sriraman,821833572,2933 Bunker Hill Ln,Foster City,CA,94404
6070 0.65 Goldstein,Sam,,16106 La Avenida Dr,Silver City,IA,51571
In the above search, you passed a first-name, last-name and a cross-field weight. Here, 1.0 indicates full weight was given to cross-fielded data. If you reduce that weight, you see reduced scores on the results with cross-fielded data:
java -cp java\lib\TIB_tps_java_interface.jar java/sample/src/QryCognate.java person Sam Sang 0.8
You can see the following results.
key score record text
1636 0.97 Sangsom,Sam,,2015 Manhattan Beach Blvd,San Diego,CA,92154
903 0.67 Sarmad,Sam,300461920,12479 Research Pkwy,Salt Lake City,UT,84112
1053 0.66 Sabesan,Sriraman,821833572,2933 Bunker Hill Ln,Foster City,CA,94404
6070 0.65 Goldstein,Sam,,16106 La Avenida Dr,Silver City,IA,51571
6338 0.60 Nam,Sang,,5445 Dtc Pkwy Ste 600,Sunnyvale,CA,94088