AW: How to narrow SQL search with multiple criterias?

Bob Sneidar bobs at twft.com
Wed Jan 26 13:04:18 EST 2011


Also remember that Trevor's sqlYoga has the ability to create query objects, and then update those objects and re-run the query. The API builds the SQL for you, so it's very easy to do recursive queries. 

As an aside, there is a way in mySQL (and others I am sure) to output the query as a new temporary table, assuming you have CREATE TABLE rights to the database. This can be a better way to handle large datasets. 

For instance, let's say you want the detail for every invoice in the prior year sorted by item code, and subtotalled for each item and totaled at the end. If the business is a very busy and profitable one, that could be a helluvalotta data! (Assume you are CDW for a moment). You would never want to return that much data to a memory variable! 

By having the engine output to a temp table you can then run the report on that, and never have to worry about overwhelming Rev. 

Bob


On Jan 26, 2011, at 9:34 AM, Peter Haworth wrote:

> I think the problem with using LC to narrow down the results of an SQL SELECT is that the database may have changed since doing the initial SELECT so what you end up with may not reflect the current state of the database.  I guess that may not matter to your application, or maybe you've locked the database so it can't change but definitely a consideration.
> 
> I've been doing this by saving the contents of the WHERE Clause for the SELECT in a variable then appending the additional selection criteria to it and issuing a new SELECT statement with the revised WHERE clause.
> 
> Don't have an opinion on the efficiency aspects of LC vs SQL, I suspect it depends on a lot of factors.
> 
> Pete Haworth
> 
> On Jan 26, 2011, at 8:57 AM, Tiemo Hollmann TB wrote:
> 
>> Hi William,
>> That's what I thought, to cycle through the SQL result data with LC, but was
>> unsure how performing it is. I have let's say 20000 records and have to
>> check multiple properties with multiple values. Will the result of my cycle
>> still be so fast, that the user doesn't has to wait for it, because it
>> should just be a nice usability feature to disable the remaining selections
>> and is not necessary for life. I was unsure, if cycling through the result
>> with LC would be state of the art to realize such feature.
>> Probably I have to code it and make real performance test with and without
>> this feature. Perhaps the SQL Select is so much more time consuming, that my
>> LC cycle isn't relevant anyway.
>> Thanks
>> Tiemo
>> 
>>> -----Ursprüngliche Nachricht-----
>>> Von: use-livecode-bounces at lists.runrev.com [mailto:use-livecode-
>>> bounces at lists.runrev.com] Im Auftrag von william humphrey
>>> Gesendet: Dienstag, 25. Januar 2011 17:55
>>> An: How to use LiveCode
>>> Betreff: Re: How to narrow SQL search with multiple criterias?
>>> 
>>> Sometimes, SQL failure that I am, I just dump it all in a variable and
>>> look
>>> through it using LiveCode. It depends how big although LiveCode is
>>> pretty
>>> quick.
>>> 
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode





More information about the use-livecode mailing list