--- 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.