At 11:01 PM 11/11/2003 -0500, you wrote:

> >
> > Use RDB$DATABASE instead of ABNTRADEDATA as the last reference - there is
> > only one record in RDB$DATABASE
> >
> > SELECT (select avg(volume) from ABNTRADEDATA where volume >0), (select
> > avg(volume) from abntradedata where volume <0) AVG_VOLUME from
> > RDB$DATABASE;
> >
> > Alan
> >
>
>I could be Forrest Gumping here,

Yes. :-))

>but I don't get how RDB$Database
>relates to the query.... Just sort of a place holder?

In fact, RDB$Database is a system table that hold one and only one
record. When we use it this way, we care not what it stores. Its
usefulness is *because* it contains one and only one record.

You could create your own one-record table called DUAL, if you liked. We
use rdb$database the same way Oracle users use DUAL. :-)

As long as you only want to get computed results back, the only important
thing is to make sure you do the query FROM a single-record table. If you
use any table with multiple records, you'll get multiple (identical) records.

So it's a useful technique when you want to pull things like subquery
results, context variable values, generator values, using a SELECT
statement, without FROMming a meaningful table reference.

h.