Search code examples
sqlfoxpro

Foxpro SQL Query for > 24 Criteria

I'm using OleDB to connect to a FoxPro DBF database. I need to query a table for all items with column = key1 or key2 or key3...... key2000.

FoxPro apparently doesn't like long queries....

I've tried both:

where (col like "key1") or (col like "key2") or ..... (col like "key2000")

and 

where col in ("key1", "key2", "key3", "key4".... "key2000")

The first solution fails for "query too complex". The second key fails for too many elements in the in range (apparently the max is 24). This seems absurd...

Is there a way for me to construct my query without multiple reads?

Thanks.

Solution

  • You need to push your criteria into a temporary table or criteria table and query from that

    Create Table Criteria  (
                        UserSessionId ...
                        , KeyValue ....
                        )


Select ...
From MyMainTable
    Join Criteria
        On Criteria.KeyValue = MyMainTable.col
            And Criteria.UserSessionId = ...