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@...>
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.
>
>