PRB: Parameterized View Discrepancy Using IN() Filter Criteria

• Microsoft Visual FoxPro for Windows 5.0

SYMPTOMS

A Parameterized View returns incorrect results when assigning multiple string values to a parameter of the IN() Filter criteria.

CAUSE

The View Designer is not designed to handle queries where multiple values are assigned to a parameter in the IN() clause.

WORKAROUND

To use View Designer to create a parameterized View that returns the correct values when using the IN() filter criteria, you need to specify multiple parameters instead of a single one. For example, you would specify the following in the Filter tab of the View:

   Field Name = "customer.customer_id"
   Criteria = IN
   Example = ?a, ?b, ?c

   SELECT *;
     FROM tastrade!customer;
     WHERE Customer.customer_id IN (?a, ?b, ?c)

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Issue the following command in the Command window:

       MODIFY DATABASE HOME()+"samples\tastrade\data\tastrade.dbc"
   

2. Create a new View using the View Designer.

3. Add the table Customer to the View.

4. Select all fields for output.

5. In the filter section, enter customer.customer_id" as the "Field

       Name," select "IN" as the Criteria, and enter "?lcKeyList" as the
       Example.
   

6. When you click the View SQL option, you should see the following:

       SELECT *;
          FROM tastrade!customer;
          WHERE Customer.customer_id IN (?lcKeyList)
   

7. Save the View.

8. In the Command window, execute:

       lcKeyList=' "ALFKI,""ANATR,""ANTON" '
   

9. Browse the View.

10. Note that you get an empty cursor. Close the Browse window.

11. Again in the Command window, release lcKeyList by executing:

        CLEAR ALL
    

12. Browse the View.

13. When asked for the value for lcKeyList, enter "ALFKI," "ANATR,"

        "ANTON."
    

14. Note that you get a single record. Close the Browse window.

15. In the Command window, execute:

        lcKeyList=' "ALFKI","ANATR","ANTON" '
    

16. In the Command window, execute:

        SELECT * FROM tastrade!customer
           WHERE customer_id IN (&lcKeyList)
    

17. Note that a cursor with three records is returned.