Hi Graeme,

It work now!

Thanks for help.

--- In firebird-support@yahoogroups.com, "Graeme Edwards" <g.edwards@
...> wrote:

>
> Is it because you have the wrong table name Detaim in the from

clause.

>
> Try removing the join at the end. I assume that Detaim is meant to

be detail

> anyway.
>
> Do you need the join between Master and Detail anyway? I assume that

every

> master
>
> record would have related detail records and that
>
>
>
> From Master M
>
>
>
> would be sufficient.
>
>
>
>
>
>
>
> >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!
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
> <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+t
echnic

> al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support
>
> Computer
> <http://groups.yahoo.com/gads?

t=ms&k=Computer+technical+support&w1=Technical

>

+support&w2=Computer+technical+support&w3=Compaq+computer+technical+su
pport&

>

w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Mi
crosof

> t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA>

technical

> support
>
> Compaq
> <http://groups.yahoo.com/gads?

t=ms&k=Compaq+computer+technical+support&w1=Te

>

chnical+support&w2=Computer+technical+support&w3=Compaq+computer+techn
ical+s

>

upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+suppor
t&w6=M

> icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g>

computer

> technical support
>
>
> Compaq
> <http://groups.yahoo.com/gads?

t=ms&k=Compaq+technical+support&w1=Technical+s

>

upport&w2=Computer+technical+support&w3=Compaq+computer+technical+supp
ort&w4

>

=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Micr
osoft+

> technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical

support

>
> Hewlett
> <http://groups.yahoo.com/gads?

t=ms&k=Hewlett+packard+technical+support&w1=Te

>

chnical+support&w2=Computer+technical+support&w3=Compaq+computer+techn
ical+s

>

upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+suppor
t&w6=M

> icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA>

packard

> technical support
>
> Microsoft
> <http://groups.yahoo.com/gads?

t=ms&k=Microsoft+technical+support&w1=Technica

>

l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport

>

&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icroso

> ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>

technical

> support
>
>
>
> _____
>
> 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
> <mailto:firebird-support-unsubscribe@yahoogroups.com?

subject=Unsubscribe>

>
> * Your use of Yahoo! Groups is subject to the Yahoo!
> <http://docs.yahoo.com/info/terms/> Terms of Service.
>
>
>
> _____
>
>
>
> [Non-text portions of this message have been removed]
>