It seems I need to wait for Firebird 2.0 so I could use 'derived
table' method.
Anyway thank's a a lot

--- In firebird-support@yahoogroups.com, "Carl Peto" <carl@b...>
wrote:

> HAVING has to be done on something in the GROUP BY.
>
> So the code below wouldn't work.
>
> This should work...
>
> SELECT hinv.invoice_no, sum(dinv.subtotal) total
> FROM header_invoice hinv JOIN detail_invoice dinv ON

(hinv.invoice_no =

> dinv.invoice_no)
> GROUP BY hinv.invoice_no
> HAVING sum(dinv.subtotal) >= 100
>
> ----- Original Message -----
> From: "Svein Erling" <svein.erling.tysvaer@k...>
> To: <firebird-support@yahoogroups.com>
> Sent: Wednesday, March 31, 2004 12:00 PM
> Subject: [firebird-support] Re: Cascade Select in Firebird
>
>
> > What about something like
> >
> > select header_invoice.invoice_no, sum(detail_invoice.subtotal)
> > as total
> > from header_invoice join detail_invoice on
> > header_invoice.invoice_no = detail_invoice.invoice_no
> > group by header_invoice.invoince_no
> > having sum(detail_invoice.subtotal) > 100
> >
> > (I rarely use 'having', so I'm not 100% sure of the syntax)
> >
> > Though there is no index for the sum, so this can be slow on

largish

> > tables.
> >
> > HTH,
> > Set
> >
> > --- In firebird-support@yahoogroups.com, "h4o" wrote:
> > > Currently I'm using view as workaround like in this case:
> > > First I'm create a view:
> > >
> > > create view myview (invoice_no, total) as
> > > select header_invoice.invoice_no, sum

(detail_invoice.subtotal)

> > > as total
> > > from header_invoice join detail_invoice on
> > > header_invoice.invoice_no = detail_invoice.invoice_no;
> > >
> > > Then I need to display invoice which total is more than 100:
> > >
> > > select *
> > > from myview
> > > where myview.total >= 100;
> > >
> > > Is it possible to display the results in just one SQL

statement?

> >
> >
> > Yahoo! Groups Sponsor
> > ADVERTISEMENT
> >
> >
> >
> >
> >
> > -----------------------------------------------------------------

---------

> ------
> > Yahoo! Groups Links
> >
> > a.. To visit your group on the web, go to:
> > http://groups.yahoo.com/group/firebird-support/
> >
> > b.. To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
> > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms

of Service.

> >
> >