--- In firebird-support@yahoogroups.com, "constantijnw" <cw.s@x> wrote:

>
> --- In firebird-support@yahoogroups.com, "ra8009" <ra8009@y...> wrote:
> >
> > My user wants to select a specific category or ALL categories. They
> > may have 100,000 to 200,000 records and speed will be important as
> the
> > user may run this hundreds of times per day.
> >
> > Is the best way to hanldle this (I'm using IB Objects) to:
> >
> > a) Build the SQL statement dynamically with the "where" clause or
> > without as needed
> >
> > b) Use "Between" with two paramters. When one category is needed I
> > make them the same, when ALL are needed I set them to high and low
> > values above and below the range.
> >
> > c) some other method
>
> Don't know if it is best, but I think it is better than a) or b):
>
> use a stored procedure with CATS as inputparameter:
>
> IF (CATS IS NULL) THEN /* a way to denote ALL */
> FOR
> SELECT AFIELD FROM ATABLE
> INTO :AFIELD
> DO
> SUSPEND;
> ELSE
> FOR
> SELECT AFIELD FROM ATABLE WHERE ACAT = :CATS;
> INTO :AFIELD
> DO
> SUSPEND;
>
> Or more general (all combinations of categories can be chosen):
>
> FOR
> SELECT AFIELD, ACAT FROM ATABLE
> INTO :AFIELD, :ACAT
> DO
> IF (BIN_AND(CATS, ACAT) > 0) THEN
> SUSPEND;
>
> where CATS holds categories set as binary flags.

Thank you very much! I hadn't thought of this.