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?