Open topic with navigation

TIBCO® Patterns 6.1.2

# 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
    		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 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 atop-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 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 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 “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 on 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 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 the 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 a 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

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO® Patterns

Copyright © 2010-2025. Cloud Software Group, Inc. All Rights Reserved.