Sequential filtering in SQL

[Ed Kleban]

Ok Ruslan,

Time for a brand new topic.

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.

That make sense?

Any suggestions?