PRB: SELECT-SQL Command Ignores SET FILTER Condition

ID: Q118491

The information in this article applies to:

• Microsoft FoxPro for Windows, versions 2.0, 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for Macintosh, version 2.5b

SYMPTOMS

The SELECT-SQL command does not honor a filter condition that was established with the SET FILTER command.

CAUSE

The SELECT-SQL command operates directly against the specified tables and is therefore not influenced by the existence of a filter condition.

RESOLUTION

Instead of using SET FILTER to exclude a set of records, use the WHERE clause of the SELECT-SQL command to exclude a set of records.

The following example illustrates the behavior and the resolution:

   * Create a sample table

   CREATE TABLE test (one C(2))
   INSERT INTO test (one) VALUES ("AA")
   INSERT INTO test (one) VALUES ("BB")
   INSERT INTO test (one) VALUES ("BB")
   INSERT INTO test (one) VALUES ("CC")

   * Establish a filter to exclude all "BB" records

   SET FILTER TO one <> "BB"

   * Show the results of the filter:

   LIST

   * The following SELECT statement ignore the
   * filter, it will retrieve all records:

   SELECT * FROM test

   * Instead of using the filter, exclude all "BB" records using
   * the WHERE clause of the SELECT command:

   SELECT * FROM test WHERE one <> "BB"

STATUS

This behavior is by design.

MORE INFORMATION

The "SET FILTER" topic in the online Help file states that the SET FILTER command "specifies a condition that records in the current table/.DBF must meet to be accessible." The Help entry further states that "All commands that access the table/.DBF respect the SET FILTER condition."

These statements are accurate for all commands except the SELECT-SQL command. The SELECT-SQL command does not honor a filter condition established with the SET FILTER command.