Microsoft KB Archive/118491

{| = PRB: SELECT-SQL Command Ignores SET FILTER Condition =
 * width="100%"|

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.

Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b 2.60 docerr KBCategory: kbprg kbprb kbdocerr KBSubcategory: FxprgSql
 * }