It may be correct (excepting that you probably have to cast your parameter), but it will be slow if you have many invoices (less than a six or seven digit figure may be no problem). At prepare time, the optimizer calculates how to fetch the records and since you refuse to tell it until later it has to search all records (NATURAL). Basically, you ought to split your search into two separate statements:

select i.* from invoices i
join selected_customers s on i.id_customer = s.id

and the other statement simply

select i.* from invoices i

And then choose one or the other depending on whether you want the entire table or a subset. The second select may still be slow, but the user should expect things to be slow if they ask for a gazillion of records.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of shg_sistemas
Sent: 14. januar 2010 14:52
To: firebird-support@yahoogroups.com
Subject: [firebird-support] selecting some records from table

Hello, I need to do a simple Selling by Customers report, but the user has to be able to select some of the customers or all of them.

So I have a temp table (on commit delete rows) with a field "ID" and I just copy the IDs of the selected customers to it.

Then I construct my select like this:

select * from invoices
where
(
:All_Customers = -1
)
or
(
exists
(select ID
from selected_customers
where invoices.id_customer = selected_customers.id)
)

So, if I want all the customers I just set All_Customers = -1

The question is: is that the correct (and *BEST/FASTEST*) way to do it??

Just in case, I use FB2.1

Thanks!

Sergio