Bret,

Again, I want to thank you for this clarification.

My real situation is:
We have several huge normalized tables (~1GB-4GB) with our data.
We also have several lookups tables. Lots of them contain only few (less than 15) records. But 3 (Towns, Usage, Lenders) are pretty large (200 - 1000 records). We assume, that our users may select from multilists, based on these lookups tables. They will not select more than 50 items.
Multilists have an Exclude option (the usage of it, I believe, would be seldom).

In my current implementation I analyze the list of comma-delimited codes (as a long string) and if I have more than 24, I reconstruct my criterion as
SearchFiled IN (ListOfComma-DelimitedCodes), otherwise I use INLIST(SearchField,ListOfComma-DelimitedCodes).

I got the Error message, which I described, for 32 codes. I tested this on only one table (exclude all joins) and I got this message again.
So, the problem is in IN clause. I'm a little confused right now, what should I do.

1) Do I need to play with SYS(3055) function?

2) Do I need to create cursors, add additional Joins and make these two app share the same DS?

3) Do I need to create cursors, add subqueries in my Query and make these two app share the same DS?

First approach doesn't require any changes in my current class design.
2) and 3) require couple of changes, but I know, how I can implement this...



>>Thanks a lot for this clarification. I was really confused with this VFP misleading message (BTW, it should be fixed in VFP 7.0, I hope).
>
>SQL statements can get so complicated that I don't blame MS for failing to parse every one of them completely for rigorous syntax checking.
>
>>
>You got the somewhat misleading error message 'SQL command is too complex' because you put something in your IN() clause that was not a valid SQL statement.
>
>As Ed says, apparently your usage IS allowed, and I was wrong. Good thing he was watching. I suppose that manipulating SYS(3055) might actually solve your immediate problem. But if you don't really know how long the list of values might become, and if Ed says that using IN() that way is buggy, then it sounds like serious trouble.
>
>After actually consulting VFP help, I couldn't find any documentation of IN () either as a SQL clause or as a VFP function. However, examples 7 and 8 of the SELECT - SQL help topic show the use of NOT IN () with a list of values, just the way you were using IN (). The Hacker's Guide has this to say:
>
>"WHERE has two comparison operators not found elsewhere in FoxPro: IN and BETWEEN. IN checks whether the specified field or expression is contained in a given list. (IN is a lot like FoxPro's INLIST() function.) It's handy for checking things like whether the customer's state is one of some subset, like ("PA", "NJ", "DE"). BETWEEN checks whether the field or expression is in the specified range—it works pretty much like FoxPro's BETWEEN() function and they seem to be equally fast. Both BETWEEN and BETWEEN() are inclusive, meaning that matches to the specified boundaries are included in the result. Like FoxPro's BETWEEN(), the BETWEEN clause does require proper placement of the lower and upper boundaries—it won't work if you reverse them.
>
>The WHERE clause also may contain sub-queries, which are simply queries within another query. The sub-query is executed and then some comparison is made to its results. SELECT has several special operators for performing these comparisons: IN, EXISTS, ALL, and ANY or SOME. Of these, we've only found a real need for IN, which lets you see whether a particular value occurs in the results of a sub-query. We're actually far more likely to use NOT IN to find records that don't have a match in a sub-query. This is the best way to find the differences between two tables—all the records contained in one, but not in the other."
>
>The advice the others have given you about how to get the choices from one form to another is as good as anything I might give. I agree that you should test the speed of the different ways of selecting. My data files are small, so I can't test these things as easily.
>
>>>By the way, if you're interested, you might search for Bruce Campbell's thread asking about alternatives to the NOT IN () clause, which is not Rushmore
>>
>>Could you please tell me, what exactly should I serach?
>>
>I see you already found Thread #306158. The gist of it was that although NOT IN () is very slow, all the alternatives are slower. VFP's SQL engine is indeed doing the best it can. If the users have a logical requirement to use such a query, a warning message about its slowness might be appropriate.