At 05:03 AM 3/03/2007, you wrote:

>Hi there,
>
>I have an INVOICES table like this:
>
>create table INVOICES
>(
> NUMBER integer,
> CUSTOMER integer,
> INVOICETOTAL numeric(10,2),
> ...more fields...
>)
>
>I set up an index like this:
>
>CREATE INDEX INV_TOTAL on INVOICES(CUSTOMER,INVOICETOTAL);
>
>Now when executing the following query, the index is picked up
>correctly in the plan:
>
>SELECT SUM(INVOICETOTAL) FROM INVOICES
>WHERE CUSTOMER=123 AND INVOICETOTAL > 0;
>
>However, for the following queries the index is NOT picked up:
>
>SELECT SUM(INVOICETOTAL) FROM INVOICES
>WHERE CUSTOMER=123 AND INVOICETOTAL <> 0;
>
>SELECT SUM(INVOICETOTAL) FROM INVOICES
>WHERE (CUSTOMER=123) AND (NOT INVOICETOTAL = 0);
>
>I'm currently using FB 1.5.3. Is there any workaround I could use to
>solve this? I need the total for invoices where the INVOICETOTAL field
>is not zero.

NOT (and <>) predicates can't use an index.

However, logically, you have no need to exclude the zero
INVOICETOTALs in this particular query, since the sum() result would
be the same with or without them (x + 0 = x).

In any case, the compound index isn't as beneficial for searches as
two separate indexes on CUSTOMER and INVOICETOTAL. Compound indexes
are sometimes useful for ordering or grouping but they don't provide
any advantage for searching. In this case the NOT in the predicate
prevents use of that index, whereas, with separate indexes, the
CUSTOMER index could be used.

If you are just experimenting, why not test
SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE CUSTOMER=123 AND INVOICETOTAL > 0;

Or, if there are negative invoicetotals:
SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE CUSTOMER=123
AND (INVOICETOTAL > 0 OR INVOICETOTAL < 0);

./heLen