>>>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 )
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up