Lester Caine wrote:

> But seriously, building a complex filter with various from and to
> elements results in a select with a where clause that will either give a
> list of answers or nothing. Adding and removing elements to the filter
> changes the 'temporary table' view which is built of a list of elements
> in the where clause - I'm not using 'IN' for this - I use the = and
> BETWEEN against a selection of available fields. The resulting view of
> the data can then be used IN an IN clause to update or delete the
> selected set of records.
> Of cause if someone wants to write and debug a long list of hand coded
> entries ... personally I prefer to get the user interface to work like
> the SeaMonkey filter selector - giving available fields and values that
> can be added to or removed as required. Think IBOConsole but with an
> expandable list for the filter page.
>

Yes, I use, of course, me too that paradigm (an adapted one). And I find
it very useful. But this only works only on *relational* data. Not for
other types. My example was about *navigational* data. We have a tree
like this:

Expenses
Production expenses
Materials
Consumables
Tools
Fixed assets
Wages
Water
Electricity
Goods
Fuels
Liquid
Solid
Food
Electronics
Medicine
Research & Development
...

This is a small piece from a typical chart of /types/ of accounts (IIRC
- I don't discuss now the correct position of accounts :) - I'm not the
one...). Each branch have a variable number of accounts underneath. If
one wants to see all the expenses accounts what do you do? (Perhaps
someone can help me... :) ) The tables structure is depicted in my
previous messages. I found (so far) three approaches:
1. Having the PK with the nodes, and building the IN clause on the
fly at the user request (ie. when he press on [Apply filter]).
2. More SQListic is to build a recursive selectable SP on the
ACCTYPE table (the table which holds the tree structure) and JOIN it
with the ACC table. But this must be called each time for each node (ie.
wh/if the user chooses to see 'Materials', 'Fuels' and 'Food'? ) which
is a performance overkill. Also, another thing which nukes this, for my
situation (at least), is simulating the NOT IN which (especially for Fb
2.0) in both cases, using either NOT EXISTS or LEFT OUTER JOIN WHERE <f>
IS NULL has (much) worse execution times. (In fact isn't surprising
because the SP must complete before the JOIN or other predicates can be
made, and, also, the SSP can be 'walked' only in natural order,
meanwhile Select * from t [not] in (<comma delimited members>) uses a PK
(ie. selectivity = 1) when available)
3. When user clicks on the check-box near to 'Expenses' the tree
(behind the scenes) updates a temporary table which can be JOINed later.
This, thus appealing, has in practice many problems due to burst updates
which such an information management tree generates (one can use threads
and/or timers to mitigate the 'spikes') but also he must pray (a lot) in
order to users to be calm enough to not (un)select to much and to quick,
their stations to sustain their actions and the server (its HDD mainly)
to catch up.

That's why I was forced to choose 1. And this is only a small example.
There are more in the information management area (for ex. data mining
and/or OLAP issues) in which the selection based on human factors
(like/dislike - interesting/uninteresting) rather than data values has a
much greater role.

> And as Roman says - this needs to be compatible across other databases.
>
>

If it is an (parametrized) IN without ranges, it is, AFAIK. (In fact,
the feature is already here and working, but 1.) we must expand to allow
more than 1500 items - thing which IMHO it appears easy to do - see my
other messages, and 2.) to make possible Select * from t where id in
(:MyPARAMETER)

hth,

m. th.