Which Firebird version are you using? I think Firebird 2.1 allows you to
do things like:

WITH CP as
(Select First 12 S2.CODPRODUCT
From SALES S2
where S2.DATE BETWEEN :DATE_INI AND :DATE_END
group by S2.CODPRODUCT
ORDER BY Sum(S2.VALUE) DESC)

Select Sum(S.VALUE) As VALUE,
Coalesce(SUBSTRING(P.NAME FROM 1 FOR 20), 'OTHERS') AS NAME
From SALES S
Left Join CP on S.CODPRODUCT = CP.CODPRODUCT
Left Join PRODUCTS P On CP.CODPRODUCT = P.CODPRODUCT
where S.DATE BETWEEN :DATE_INI AND :DATE_END
Group By NAME

WITH is an addition to Firebird that I think can make many statements
look considerably simpler. Note that I do not actually use Firebird 2.1
yet, so there may be mistakes in my syntax.

As for your original question, Firebird used to evaluate IN (subselect)
for every potential row, but I think that between version 1.0 and 1.5 it
started translating such queries into EXISTS queries when possible.
Though I have no clue whether subselects with aggregation can or are
optimized in any way.

HTH,
Set

emb_blaster wrote:

> hi to all,
> I=B4m making a pie chart (like MS Excel) of X most sold products and
> group the others. But every time I execute the SQL the firebird became
> slow...
> I don=B4t think, but maybe my SQL is too complex :( , or there=B4s a bug
> :? . can anyone help?
> take a look, lets say that X is 12 then:
>
> Select
> Sum(SALES.VALUE) As VALUE,
> SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> From SALES
> Inner Join PRODUCTS On SALES.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> where (SALES.DATE BETWEEN :DATE_INI AND :DATE_END) AND
> (SALES.CODPRODUCT IN (Select First 12
> SALES.CODPRODUCT
> From SALES
> where (SALES.DATE BETWEEN :DATE_INI AND
> :DATE_END)
> group by SALES.CODPRODUCT
> ORDER BY Sum(SALES.VALUE) DESC))
> Group By NAME
> UNION
> Select
> Sum(SALES.VALUE) As VALUE,
> 'OTHERS' AS NAME
> From SALES
> where (SALES.DATE BETWEEN :DATE_INI AND :DATE_END) AND
> (SALES.CODPRODUCT NOT IN (Select First 12
> SALES.CODPRODUCT
> From SALES
> where (SALES.DATE BETWEEN :DATE_INI AND
> :DATE_END)
> group by SALES.CODPRODUCT
> ORDER BY Sum(SALES.VALUE) DESC))
> Group By NAME
>
> the first part of Union slow too, maybe it execute the IN (SELECT ...)
> for every row. But with a "static IN" it runs good. See:
>
> Select
> Sum(SALES.VALUE) As VALUE,
> SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> From SALES
> Inner Join PRODUCTS On SALES.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> where (SALES.DATA BETWEEN :DATA_INI AND :DATA_END) AND
> (SALES.CODPRODUCT IN (100,358,24,227,359,360,94,1712,395,6,450,357))
> Group By NAME
>
> the way to correct I found is making a SELECT ... FROM SELECT see below:
>
> SELECT
> VALUE,
> NAME
> FROM (SELECT FIRST 12
> SUM(SX.VALUE) AS VALUE,
> SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> FROM SALES SX
> INNER JOIN PRODUCTS ON SX.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> WHERE (SX.DATA BETWEEN :DATA_INI AND :DATA_END)
> GROUP BY NAME
> ORDER BY SUM(SX.VALUE) DESC)
>
>
> I don=B4t know if this is a standard behavior of firebird. Is it? I
> think my first SQL is more "beauty" and clean to understand. There=B4s
> anothers ways? I searched in the messages but don=B4t found any light.
> thanks.