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]