CursorAdapter Select Command Where Clause

To set CursorAdapter filters, you must construct a WHERE clause that contains filters and add it to the CursorAdapter Select command that retrieves data from the data source.

WHERE Clause for Character Data

The WHERE clause format in VPME for CursorAdapters is the following when all fields to be filtered are character type:

WHERE

 <field or expression> LIKE ?PKValue AND

<field or expression> LIKE ?ParentPKValue AND

<field or expression> LIKE ?RI<fieldname> AND

<field> LIKE ?FIND<fieldname> AND

<field or expression> LIKE ?GET<any identifying string>

WHERE Clause For Non-Character Data or Value Range

If the filter field or expression is not of character type or if the field or expression is filtered on a range of values, the appropriate variable name suffixes (_cMin, _cMax, _nMin, _nMax, _tMin, _tMax) must be used.

For example, a numeric primary key field would be filtered as follows:

<field or expression> >= ?PKValue_nMin AND

<field or expression> <= ?PKValue_nMax

To create the filters for the WHERE clause, you must enter:

·       Field or Expression: Start a filter line with the field or expression you need to filter.

·       Criteria: Enter a comparison operator.

·       Filter Variable: Enter a filter variable using one of the special variable names.

·       Logical: Add an AND or OR condition to the end of the filter line when you have more than one filter

 

How to Determine the Criteria Operator and Filter Variable Entries for a CursorAdapter Filter

Your field or expression entry for a filter in the WHERE clause depends on which of the five types of filters you are setting. The entry is either a primary key, foreign key, SQL Find Form field or a field you need to filter.

Your criteria operator and your filter variable entries for a filter depend on the data type of the field or expression you enter first. The following table shows the Criteria operator and filter variable entries you must use for different field / expression data types.

 

Field / Expression
Data Type

CursorAdapter Filter Criteria Operator
and Filter Variable Entries

Character

Like ?VariableName

Character, filtered on a range of values

Enter the following two Filter lines joined with Logical "AND":

>=  ?RangeVariableName_cMin

<=  ? RangeVariableName _cMax

Date or Datetime

Enter the following two Filter lines joined with Logical "AND":

>=  ? VariableName _tMin

<=  ? VariableName _tMax

Numeric, Float, Double, Integer

Enter the following two Filter lines joined with Logical "AND":

>=  ? VariableName _nMin

<= ? VariableName _nMax

(1) Where VariableName is one of the following filter variable names:

·       PKValue – primary key filter.

·       ParentPKValue – foreign key filter.

·       RI<fieldname> - multiple foreign key filter.

·       Where <fieldname> is the name of the foreign key field or any field in the foreign key expression.

·       Example: For a character foreign key field named “TITLE” in the CursorAdapter, the entry would be “Like ?RITITLE”.

·       FIND<fieldname> - field to be used for a SQL Find Form filter.

·       Where <fieldname> is the name of the filtered field.

·       Example: For a character filtered field named “EMPLOYEEID” in the CursorAdapter, the entry would be “Like ?FINDEMPLOYEEID”.

·       Getstring -  developer definable filter.

(2) And where RangeVariableName is either the FIND<fieldname> or the Getstring variable name. A character field type filtered on a range of values only makes sense in the context of these two types of filters.

Tip - ?: Notice that the variable name for a filter is preceded by a "?". The "?" tells Visual FoxPro that the entry is a variable, not a constant or literal.

Tip – Logical: When you create more than one filter using the special variable names, join the filters with the "AND" operator in the Logical column.

Tip – Case-Insensitive: To make a filter case-insensitive the UPPER() function must be used. For example, a FIND filter can be made case-insensitive as follows: UPPER(<field>) LIKE UPPER(?FIND<field>)

Character Criteria Operators

When creating filtered CursorAdapters, you use the Filter criteria operator "Like" when you want to allow partial matches or "==" when you want an exact match for a character data type.

VPME will append the "WHERE Wildcard" character entered on the Data Sources form in the Data Builder to the partial character value to make the partial match succeed. For example, for SQL Server, the WHERE Wildcard character is “%”. See Data Sources.

Forms – NoData CursorAdapter Property

When you use a filtered CursorAdapter with a form, you must set the CursorAdapter's NoData property to .T. to prevent Visual FoxPro from displaying prompts when a form loads.

More:

WHERE Clause Example