Sequential filtering in SQL

[Ruslan Zasukhin]

On 12/11/05 11:34 PM, "Ed Kleban" <Ed at Kleban.com> wrote:

> I starting to work on the part of the design that will use the bit_and( x, y
> ) operator you have provided to allow me to do some masked filtering.  I
> need to apply multiple filters in multiple separate passes to take a big
> list and make it smaller and smaller with each filter pass until I have only
> the records I want remaining.
> 
> If bit_and were available as an API call, this would be very simple.
> I would simply do a Find to get a VSet, then do more Finds using the
> previous VSet result as an "inSet" argument for the next filter pass.
> 
> But unfortunately bit_and is not an API call, it is an SQL call.  So I need
> to figure out how to do this in SQL.
> 
> There are two intuitive ways that come to mind:
> 
> 1) Make a nested SQL SELECT Statement with a WHERE inside of a WHERE inside
> of a WHERE to apply all the filters.  Unfortunately I need to keep the
> result of the first filter (the outside WHERE) around in memory so that I
> can one of many different second-level filters on this smaller list at any
> time.  So nesting WHERE statements does not get me what I need.
> 
> 2) See if I can find an some way to make a SELECT statement take an inSet
> argument like the API Find calls do.  I.e. a list of records that the search
> is restricted to.   Unfortunately, I have found no such declaration in the
> SQL manual.
> 
> 3) Take the resulting Cursor I get back from the first Select, and build a
> new temporary in-memory table with the results of that cursor so that I can
> do the next SELECT statement on that new smaller table to create yet a third
> smaller table, and so forth.  Unfortunately, I have not found any efficient
> command for telling either SQL or the API: "Take that VCursor that I just
> got back from the SELECT statement and turn it into a table."  The only way
> I see to make a table from the result of a Select is to manually build a
> table and then copy the contents of the Cursor records into the new Table
> records one at a time in a loop.
> 
> So... Unless you have a recommendation as to something more efficient I
> might try, my only option at this time seems to be the "manually build a new
> table with a loop from a Vcursor" technique for every level of filter I want
> to use.

About SQL. Why you cannot make SQL query which combine all your required
filters by AND 

    WHERE bit_and(f1,..) AND bit_and() AND bit_and ...



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]