Thanx for all your help guys, this worked perfectly:

SELECT N.vnd_cod,
C.cli_nome,
SUM(COALESCE((P.pro_comissao * I.item_qtde),(COALESCE(P.pro_com_porcento,
V.ved_comissao) * I.item_valor) / 100))
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, C.cli_nome
ORDER BY N.vnd_cod ASC

On 2/7/06, Svein Erling Tysvær <svein.erling.tysvaer@... >
wrote:

>
> 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?
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
> SPONSORED LINKS
> Technical support<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> Computer
> technical support<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> Compaq
> computer technical support<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> Compaq
> technical support<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> Hewlett
> packard technical support<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> Microsoft
> technical support<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>
> ------------------------------
> YAHOO! GROUPS LINKS
>
>
> - Visit your group "firebird-support<http://groups.yahoo.com/group/firebird-support>"
> on the web.
>
> - To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com<firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> - Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/> .
>
>
> ------------------------------
>

[Non-text portions of this message have been removed]