>>>>>>>Hi,
>>>>>>>
>>>>>>>I have a place in the program where the SQL Select (that selects records for a Cursor Adapter) is built dynamically as follows:
>>>>>>>
>>>>>>>

>>>>>>>select * from MyTable where OrderNo in (Number1, Number2, .... )
>>>>>>>

>>>>>>>
>>>>>>>The part in parenthesis is built dynamically and the user can select pretty much any number of order numbers to include. So if the entire SQL Select becomes too long, it bombs. Any suggestions on how I could simplify this but still allow user to select any number of "orders"?
>>>>>>>
>>>>>>>TIA
>>>>>>
>>>>>>The syntax for doing so would be a little adventurous but ... you can create a temporary table in SQL Server with the list of numbers, and have your main query use that as an IN SELECT target. That should be more optimizable than what you have.
>>>>>>
>>>>>>Hank
>>>>>
>>>>>Interesting approach. But I am not sure it would work for my case. I will have to see.
>>>>>Thank you.
>>>>
>>>>Hank's idea is the "correct/proper" approach but it requires server-side changes in SQL Server. Another, less optimal variation would be:
>>>>
>>>>- Have a local view open that contains *all* orders
>>>>- Build a *local* cursor with the order numbers selected by the user
>>>>
>>>>Then your SQL could be something like
>>>>

>>>>SELECT * FROM {View} WHERE OrderNumber IN ( SELECT * FROM {LocalOrderNumbersCursor} )
>>>>

>>>>Since this pulls all Order information over the wire (for the View) it's not efficient/scalable for large numbers of Orders or slow connections. But it has the advantage that you can do it all client-side.
>>>>
>>>>In some cases multi-select lists are powered by cursors, where when an item is selected a cursor column such as lSelected gets set to .T.. In that case this approach would be even easier, you wouldn't even have to build a cursor to hold the selected values, you could do something like
>>>>

>>>>SELECT * FROM {View} WHERE OrderNumber IN ( SELECT OrderNumber FROM {SelectionsCursor} WHERE lSelected )
>>>>

>>>
>>>First, thank you for your detailed suggestion. But I don't think this will work for me. In my case, I have to pass this "SQL Select" string to the BIZ object which creates a Cursor Adapter. To change this, would require changing the BIZ object. I am not sure I want to do it for this particular case.
>>>
>>>I think I need to figure a way to check the length of the SQL Select, before sending it to the BIZ object, and if it is longer than allowed (by the what, what is the max length of the SQL Select string I can have?) than I will inform user to break his/her requirement into a smaller.
>>
>>As long as the local view and selection cursor are in the bizobject's scope, you may just be able to pass in the SQL string. On the other hand if the bizobject has its own DataSession then they won't be in scope.
>>
>>This strikes me as one of those cases where it pays to "do it right" the first time. It doesn't take much additional string building, length checking, messages to user on failure before the "right" approach pays for itself. It saves the user getting annoyed when they have to manually consolidate 2 or more reports to get bottom-line figures they need, because you're telling them "I can't do that" (which is sacrilege with VFP BTW ;))
>>
>>Any chance you could subclass the bizobject and add a few PEMs so it does what you need?
>
>I suppose it is possible to subclass the BIZ class and make it work for this case. The question in my mind is "is it worth the time". And I also want to "exhaust" all options of doing what I need without having to make many changes. For example, one approach I am considering is to replace "IN (Number1, Number2, ...)" - in the case when the string is calculated too long - with "WHERE 1=1". That is, get all orders into the Cursor of the CA and then process just those that are needed. Of course this WHERE 1=1 may pull about 1000 records into the cursor of the CA. But VFP 9 can handle it fairly efficiently; I think.
>So there are other options.
>I just wanted to ask if I am not missing something obvious.
>Again, thank you!

No other general approach leaps out at me. IMO the IN SELECT... approach is appropriate (server- or client-side) and offers a single code path with no potential gotchas in future aka the "warm fuzzies" ;)