Hi Fabio!

COALESCE will return the first column that is not NULL. When you get
0.00, that indicates that P.pro_com_porcento has the value 0 (or
I.item_qtde or I.item_valor), which is very different from the unknown
state NULL.

Maybe you want something like:

SELECT N.vnd_cod,
SUM(((CASE
WHEN P.pro_com_porcento > 0 THEN
P.pro_com_porcento
ELSE
V.ved_comissao
END) / 100) * (I.item_qtde * I.item_valor)) AS porcento,
SUM(COALESCE((P.pro_comissao * I.item_qtde), 0)) AS valor
FROM items_venda I
JOIN vendas N ON (N.vnd_cod=I.vnd_cod)
JOIN clientes C ON (N.cli_cod=C.cli_cod)
JOIN vendedores V ON (N.ved_cod=V.ved_cod)
JOIN produtos P ON (I.pro_cod=P.pro_cod)
WHERE V.ved_cod='2'
GROUP BY N.vnd_cod
ORDER BY N.vnd_cod ASC

Note, I have no idea whether CASE can be used like this as part of a
calculation, or whether you have to do the entire calculation within
the THEN and ELSE part.

HTH,
Set
-sorry for forgetting to trim the reply last time, this time I've only
included the relevant bits.

--- In firebird-support@yahoogroups.com, Fabio Gomes wrote:
> Thanks i made it work, but i just get a bunch of 0.00
>
> Here is my select that is working:
>
> SELECT N.vnd_cod,
> SUM((COALESCE(P.pro_com_porcento,V.ved_comissao) /100) *
> (I.item_qtde * I.item_valor)) AS porcento,
> SUM(COALESCE((P.pro_comissao * I.item_qtde), 0)) AS valor
> FROM items_venda I
> JOIN vendas N ON (N.vnd_cod=I.vnd_cod)
> JOIN clientes C ON (N.cli_cod=C.cli_cod)
> JOIN vendedores V ON (N.ved_cod=V.ved_cod)
> JOIN produtos P ON (I.pro_cod=P.pro_cod)
> WHERE V.ved_cod='2'
> GROUP BY N.vnd_cod
> ORDER BY N.vnd_cod ASC
>
> It runs and return me the vnd_cod, but all the values are 0.00
>
> Is there something wrong with it?