Hi,

> How can I use cascade select in firebird without view?

What you want is called a "derived table". Firebird
doesn't support this yet, it's more or less planned
for Firebird 2.0.

> What I mean by cascade select is like:
> select *
> from (select * from ... where ...)
> where ...
> If I'm not wrong, it works on Oracle.

Indeed, Oracle supports this.

> 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?
> I hope it could be something like this:
>
> select *
> from
> (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)
> where total >= 100;

I'm not sure, but can't you use:

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
where sum(detail_invoice.subtotal) >= 100

?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com