WHERE Clause Example

Letís build a CursorAdapter filter WHERE clause for the EMPLOYEES CursorAdapter we built earlier in this chapter. Here are the pertinent facts:

∑       PKValue Filter: The EMPLOYEEID field is the primary key for the EMPLOYEES CursorAdapter. The EMPLOYEES CursorAdapter will be used as the Initial Selected Alias of a Related Forms parent form and as the Validation CursorAdapter for foreign key fields in other CursorAdapters that contain the EMPLOYEEID field. Therefore, we need a PKValue filter on the integer field EMPLOYEEID. The PKValue filter in the WHERE clause will consist of the following two parts:

∑       EMPLOYEEID >= ?PKValue_nMin AND

∑       EMPLOYEEID <= ?PKValue_nMax

∑       ParentPKValue Filter: The POSTALCODE field is a foreign key with a Referential Integrity relationship to a table of valid postal codes. Further, the EMPLOYEES CursorAdapter will be the InitialSelectedAlias for a Related Forms Child form that will display employees who live in a particular postal code for the postal code displayed on the parent Related Form. Therefore, we need a ParentPKValue filter on the character field POSTALCODE. The ParentPKValue filter in the WHERE clause will consist of the following:

∑       POSTALCODE Like ?ParentPKValueD

∑       RI<fieldname> Filter: The COUNTRY field is a foreign key with a Referential Integrity relationship to a table of valid country names. Therefore, we have a multiple foreign key situation, and we need a RI<fieldname> filter on the character field COUNTRY.  The RI<fieldname> filter in the WHERE clause will consist of the following:

∑       COUNTRY Like ?RICOUNTRY

∑       FIND<fieldname> Filter: When the EMPLOYEES CursorAdapter is used as the InitialSelectedAlias for a form, we want the form to use a SQL Find Form that allows users to use the FIRSTNAME and LASTNAME fields to select the records to fill the cursor. Therefore, we need FIND<fieldname> filters on those two fields.  The FIND<fieldname> filters in the WHERE clause will consist of the following two parts:

∑       UPPER(FIRSTNAME) Like UPPER(?FINDFIRSTNAME) AND

∑       UPPER(LASTNAME) Like UPPER(?FINDLASTNAME)

Putting It All Together

Putting all the pieces together, the WHERE clause for our example would look like this (showing each part on a separate line):

WHERE

EMPLOYEEID >= ?EMPLOYEEID_nMin AND

EMPLOYEEID <= ?EMPLOYEEID_nMax AND

POSTALCODE Like ?ParentPKValue AND

COUNTRY Like ?RICOUNTRY AND

UPPER(FIRSTNAME) Like UPPER(?FINDFIRSTNAME) AND

UPPER(LASTNAME) Like UPPER(?FINDLASTNAME)

Note that we have joined the filters in the WHERE clause with the logical ďANDĒ operator.

More:

Inserting the WHERE Clause