I'm using selection like this:
for
select a.col1, a.col2, a.col3 ... a.colN
from table_a a
where ((-1 = :value ) or (a.pk = :value))
into ...
do begin

and works perfectly....

Regards,
Paul

--- In firebird-support@yahoogroups.com, "danny_vdw"
<danny.van.den.wouwer@...> wrote:

>
> Hi,
>
> Every developer encounters following problem in stored

procedures/queries:

>
> A user ask for something specific or all, especially in reports.
>
> Most of programmers write following queries to solve the problem:
> I use the * only for example, never use it in real applications !
>
> 1)
> for
> select *
> from table_a a
> where (a.pk = :value) or (:value = -1)
> into ...
> do begin
> <do something>
> end
>
> 2)
> for
> select *
> from table_a a
> where (a.pk = :value) or (:value is null)
> into ...
> do begin
> <do something>
> end
>
> 3)
> if (value is null) then
> begin
> for
> select *
> from table_a a
> into ...
> do begin
> <do something>
> end
> end
> else begin
> for
> select *
> from table_a a
> where (a.pk = :value)
> into ...
> do begin
> <do something>
> end
> end
>
> 4)
> for
> select *
> from table_a a
> where a.pk = :value
> union all
> from table_a a
> where (:value = -1)
> into ...
> do begin
> <do something>
> end
>
> So, what we have now is:
> (1) and (2) are bad examples because the possible index on the

search

> field is not being used because of the 'or' operation. Normally the
> report reduces the search to a natural scan.
>
> (3) Is best of two worlds and give the fastest answers, but the
> complication here is that all the work in the loop needs to be
> duplicated or transferred to another stored procedure. Probably in

a

> complex loop with a lot of in and out variables. Not very friendly.
>
> (4) Should be the best solution, no code duplication, BUT it seems
> that FB doesn't see that the expression (:value = -1) evolves into
> false when :value is different from -1 (so then second part of the
> union is executed and gives the results). This means it does a lot

of

> work too get no records in the first part of the union if :value is
> different from -1!! I'm not a big fan of interbase (the

optimization

> is not as smart as in FB2.0.x) but in these examples it does the
> correct job for it! Using union to solve this problem give really
> fast response on a IB server.
>
> So, Martin (the optimizer master :)) can you check this anomalies?
>
> It should be nice that the first part of the union should give no
> result without doing fetches to the page-buffers when :value is
> different from -1. Because in that case the where is always
> evaluating to false.
>
> Thanx for the possible replications.
>
> Van den Wouwer Danny
> Peopleware NV
>