Hi guys, sorry for flooding the thread, i made it work with this:

SELECT N.vnd_cod,
SUM((V.ved_comissao / 100) *
I.item_valor) AS ved_porcento,
SUM((P.pro_com_porcento *
I.item_valor) / 100) AS item_porcento,
SUM(P.pro_comissao *
I.item_qtde) AS valor
FROM items_venda I
JOIN vendas N ON (
N.vnd_cod=I.vnd_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

But i cant make the COALESCE work, i have to get just ONE of this 2 values:

SUM((V.ved_comissao / 100) * I.item_valor) AS ved_porcento, SUM((
P.pro_com_porcento * I.item_valor) / 100) AS item_porcento

But coalesce isnt working :(

I tried this:

SELECT N.vnd_cod,
SUM(COALESCE(((V.ved_comissao / 100) * I.item_valor),((P.pro_com_porcento*
I.item_valor) / 100))) AS porcento,
SUM(P.pro_comissao * I.item_qtde) AS valor
FROM items_venda I
JOIN vendas N ON (N.vnd_cod=I.vnd_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 works, but i just get 0.00 on the first SUM :(

On 2/7/06, Fabio Gomes <fabioxgn@...> 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?
>
> On 2/7/06, Svein Erling Tysvær <svein.erling.tysvaer@...>
> wrote:
> >
> > > SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome, SUM(...
> > > GROUP BY N.vnd_cod
> >
> > Every column not an aggregate column (e.g. SUM or MAX) must be in the
> > GROUP BY clause, i.e. you must use
> >
> > SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome, SUM(...
> > GROUP BY N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome
> >
> > I don't think there is anything wrong with your use of AS,
> > Set
> > --- In firebird-support@yahoogroups.com, Fabio Gomes wrote:
> > >
> > > Hi, i tried to make the select, but it isnt working :(
> > >
> > > Here is my select:
> > >
> > > SELECT
> > > N.vnd_data,
> > > N.vnd_desconto,
> > > N.vnd_total,
> > > C.cli_nome,
> > > SUM((COALESCE(P.pro_com_porcento,V.ved_comissao) /100) *
> > (I.item_qtde *
> > > I.item_valor)),
> > > 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 N.ved_cod='2' AND (N.vnd_data >= '02/02/2006')
> > > GROUP BY N.vnd_cod
> > > ORDER BY N.vnd_data ASC, N.vnd_cod ASC
> > >
> > > Its giving me an error in the AS, i dont know if i can use AS in the
> > SUM
> > > function.. then i tried this:
> > >
> > > SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome,
> > > SUM(
> > > COALESCE(
> > > SUM((COALESCE(
> > > P.pro_com_porcento,V.ved_comissao) /100) * (I.item_qtde *
> > I.item_valor)),
> > >
> > SUM(COALESCE((P.pro_comissao *
> > > I.item_qtde), 0))
> > > )
> > > )
> > > 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 ved_cod='2'
> > > GROUP BY N.vnd_cod
> > > ORDER BY N.vnd_data ASC,
> > N.vnd_cod ASC
> > >
> > > this give me:
> > >
> > > Dynamic SQL Error SQL error code = -104 Invalid expression in the
> > select
> > > list (not contained in either an aggregate function or the GROUP BY
> > clause)
> > >
> > > Can anyone help me with this stuff?
> > >
> > > Thanks in advance,
> > >
> > > Fábio.
> > >
> > > On 2/7/06, Fabio Gomes <fabioxgn@...> wrote:
> > > >
> > > > Hi Alexandre, thanx for your reply.
> > > >
> > > > I understood your select and i ll try to adapt for what i have:
> > > >
> > > > i have 3 commission types:
> > > >
> > > > 1 - the vendor commission percent
> > > >
> > > > 2 - the product commission value (that is a fixed value for each
> > product
> > > > that is sold)
> > > >
> > > > 3 - the product commission percent value
> > > >
> > > > my order is something like the following:
> > > >
> > > > if the product commission value isnt null, it takes this value
> > instead of
> > > > the product commission percent, and if the commission percent is
> > null too,
> > > > then it will use the vendor percent..
> > > >
> > > > But thanx, i think i already know how to do it :)
> > > >
> > > > On 2/6/06, Alexandre Benson Smith <iblist@... > wrote:
> > > > >
> > > > > Hi Fabio,
> > > > >
> > > > > Fabio Gomes wrote:
> > > > >
> > > > > >Hi all, i m new to firebird, so i don't know exactly how i can
> > make a
> > > > > >procedure or something like it to solve my problem.
> > > > > >
> > > > > >I ll try to be as clear as my english allow me to, but sorry, my
> > > > > english is
> > > > > >kinda bad.
> > > > > >
> > > > > >I have a vending control, and the vendors have a fixed
> > commission value
> > > > > is
> > > > > >used to calculate the commission value, my table structure is
> > like
> > > > > this:
> > > > > >
> > > > > >vending:
> > > > > >vendor_id
> > > > > >total
> > > > > >
> > > > > >vendors:
> > > > > >vendor_id
> > > > > >vendor_comission
> > > > > >vendor_name
> > > > > >
> > > > > >items:
> > > > > >product_id
> > > > > >vending_id
> > > > > >
> > > > > >products:
> > > > > >product_id
> > > > > >product_comission_value
> > > > > >product_comission_percent
> > > > > >
> > > > > >Actually my report calculate the commission value based on the
> > vending
> > > > > >total, but i need to make it calculate like this:
> > > > > >
> > > > > >if i have a product_comission_value, it will forget the
> > > > > vendor_comission and
> > > > > >add this value to the total commission..
> > > > > >
> > > > > >So basically it have to select the vendings from that vendor,
> > and for
> > > > > each
> > > > > >product test if the product have a commission value, if it has
> > so it
> > > > > >calculates it, if it doesn't the vendors commission is used.
> > > > > >
> > > > > >
> > > > > I'd go with a simple select liek this:
> > > > >
> > > > > The comission is a fixed value ? a percent over the total sale ?
> > I am
> > > > > missing the Value of each item, you have the Value of the whole
> > sale.
> > > > >
> > > > > select
> > > > > V.Vendor_Name, Sum(Coalesce(P.Product_Comission_Value,
> > > > > V.Vendor_Comission) * I.Value/100)
> > > > > from
> > > > > Items I join
> > > > > Products P on (P.ProdutoID = I.ProductID) join
> > > > > Vending V2 on (V2.Vending_ID = I.Vending_ID) join
> > > > > Vendors V on (V.Vendor_ID = V2.Vednor_ID)
> > > > > group by
> > > > > V.Vendor_Name
> > > > >
> > > > > It could have some miswritten table name or column name, but the
> > point
> > > > > that solves your problem is that one:
> > > > > Sum(Coalesce(P.Product_Comission_Value, V.Vendor_Comission) *
> > I.Value
> > > > > /100)
> > > > >
> > > > > Coalesce returns the first argument if it is not null or the
> > second
> > > > > otherwise.
> > > > >
> > > > > As you said:
> > > > > "test if the product have a commission value, if it has so it
> > calculates
> > > > >
> > > > > it, if it doesn't the vendors commission is used."
> > > > >
> > > > > I assume that if the product has any value it should be used on
> > the
> > > > > comission calculation, otherwise (if it is null) use the
> > > > > vendor.comission
> > > > >
> > > > > I don't know if
> > > > > P.Product_Comission_Value and V.Vendor_Comission are real
> > (absolute and
> > > > > static) values or a percenth of the total, I assume it is a
> > percent, but
> > > > >
> > > > > you get the idea.
> > > > >
> > > > > >is it clear?
> > > > > >
> > > > > >How do you guys think its the best way to do it? with a
> > procedure? with
> > > > > >normal selects and coding by hand? i m kinda lost with this
> > stuff.
> > > > > >
> > > > > >Thanks in advance,
> > > > > >
> > > > > >Fábio.
> > > > > >
> > > > > >
> > > > >
> > > > > see you !
> > > > >
> > > > > --
> > > > > Alexandre Benson Smith
> > > > > Development
> > > > > THOR Software e Comercial Ltda
> > > > > Santo Andre - Sao Paulo - Brazil
> > > > > www.thorsoftware.com.br
> > > > >
> > > > >
> > > > >
> > > > >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > > > >
> > > > > 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]
> > >
> >
> >
> >
> >
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > 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]