Hi Radu,

I must use SQL statement, because I write these SQL when I implement
my app on the customer IS, but I must leave customer to easy change
some thiks in SQL without me!
If I make a SP, imagine that phone call that will help him to change
something in the SP! When I implement that option in my app that will
be great option!

Thanks again for help!

--- In firebird-support@yahoogroups.com, Radu Sky <skysword76@...>
wrote:

>
> majstoru wrote:
> > Hi,
> >
> > I have a master/detail setup for invoices! It is a classic invoice
> > strusture where master table represent a header of invoice, and a
> > detail table represent items of master table!
> > So here is a sample of master:
> > id no date customer
> > 1 10 13.03.06 customerxzy
> > 2 15 14.03.06 customerabc
> > 3 20 15.03.06 customerxxx
> > and detail sample:
> > id mid article qty price type
> > 1 1 art123 1 100 X
> > 2 1 art554 1 150 M
> > 3 2 art123 2 100 X
> > 4 3 art556 3 200 X
> > 5 3 artfff 1 150 S
> > And here is a final structure that I must get with SQL statement:
> > id no date customer col_a col_b col_c
> > 1 10 13.03.06 customerxyz 100 150 0
> > 2 15 14.03.06 customerabc 200 0 0
> > 3 20 15.03.06 customerxxx 600 0 150
> >
> > Like you can see, this resultset is contain id, no, date, customer
> > fields form master table and col_a -> sum(qty*price) where type =

X

> > and col_b -> sum(qty*price) where type = M and col_c ->

sum(qty*price)

> > where type = S!
> >
> > I try with:
> > SELECT M.Id, M.No, M.Date, M.Customer,
> > (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.

Type

> > = 'X') AS Col_a,
> > (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.

Type

> > = 'M') AS Col_b,
> > (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.

Type

> > = 'S') AS Col_c
> > FROM Master M, Detaim D
> > WHERE D.Mid = F.Id
> >
> > BUT it is a wrong SQL statement, where im my error!
> >
> > Thanks for help!
> >
>
> Why JOIN?
> Unrelated to your problem, but in your case a SP will be faster as

you

> need 3 parses in you current query and as SP will only use one
>
> HTH
> Radu
>