Bret,

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). So, now I have to consider only first approach, which I proposed:
creating cursors on the fly.


>The manipulation of SYS(3055) (which you suggested in your reply to Ed) doesn't apply to your problem. 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. You had just a list of strings, similar to what the INLIST() function expects. A "SQL syntax error" message or something would have been more helpful, but these things happen. Anyhow, IN() is not a function like INLIST(), it's a clause of a SELECT - SQL which is supposed to contain another SELECT -SQL, that is to say, the subquery. If that subquery were very complicated (how complicated, I don't know), the limits could be affected by SYS(3055), I suppose. But you don't actually have that problem.


How many subqueries can we have in one query? Is there a limitation also?


If you have to plan for more than 24 criteria strings, you'll need code to put them in a cursor and to get to that cursor from your other form (which you mentioned in your reply to Ed). Might as well forget about INLIST().


Right now I reconstruct my criteria this way:
If I have only one code, selected by user, I use:
myField=SelectedCode as where expression,
for <=24 codes:
INLIST(myField, Comma-delimited list of codes)
for >24 codes:
myField IN (Comma-delimited list of codes), which, as I understand now, is wrong.

I think, I'd leave the first condition without changes.
Now I have a question: what is faster: INLIST() or SubQuery?
Do you know the answer?
If INLIST(), I should leave this as it it also. If not, I need to change both last cases to be
myField IN (Select code from CursorName).


>The subquery is rushmore optimizable and can pull as big a cursor as you like, up to the usual vfp table limits. However, wherever you have
>
>SELECT yourfields FROM yourtable WHERE searchfield IN (SELECT searched FROM searchcursor)
>
>you can also use
>
>SELECT yourfields FROM yourtable INNER JOIN searchcursor ON searchfield = searched
>
>which may actually be faster (and Ed probably knows if it is).

It's also very important. I may implement this case. Do you know what is faster? Did somebody test the speed difference for huge (~1-4GB)databases?

AFAIK, each additional join takes time and I already have 5 or 6.
Is there a limitation of number of Joins also?


>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?

optimizable. Many were suggested and tested, and none were any better. Good thing you don't need that.

Actually, it's my case also. At least, my multilists have Exclude option (even if users will use it very rarely), so I'm very interested.

TIA

>
>>Bret,
>>
>>>I have a feeling she was thinking of INLIST() when she used IN() that way. If the sequence of strings is short and constant, INLIST() would be easier. The help menu made me wonder if it is rushmore optimizable when INLIST() contains more than two parameters. My quick test confirms that it is.
>>
>> INLIST() function has 24 codes limitation (BTW, Ed Pikman was the first person, who let me pay attention here). I need 32 codes. In my wherexp I analyze the string lnOccurs=Occurs(lcString,',') and if it's more than 24, reconstruct user's criterion using IN function.
>>
>> My questions are:
>> 1) Do you know about limitation in using IN and what is it?
>> 2) Did you compare usage of subquery vs INLIST() function and what runs faster?
>>
>> TIA
>>
>>
>>>If the sequence of strings was long or variable, using a subquery (with an actual SQL statement in it), as you suggest, would be better.
>>>
>>>>You may build a cursor on fly with records 'BROO','FOXB' etc and then
>>>>select town from towns where town IN (select town from mycursor)
>>>>
>>>>
>>>>>Walter,
>>>>>
>>>>> Recently I found another problem with SQL. In my case the Error message was 'SQL command is too complex'. I did a simple query like:
>>>>>select town from towns where town IN ('BROO','FOXB',etc.), 32 codes.
>>>>>Is my understanding correct, what I can not use IN function in SQLs? What the limitation of it's usage?
>>>>>
>>>>>TIA
>>>>>
>>>>>>