At 05:54 AM 10/02/2006, Bill Oliver wrote:

>Hi Helen, everybody,
>
>I have this query that uses the INTERSECT operator. Any suggestions
>for how to revise it to Firebird SQL?
>
>SELECT DISTINCT
> table1.clientid AS CLIENTID
>FROM
> CUSTOMER1 table1 Inner join ALLACCT table2 on table1.clientid
>= table2.clientid
>WHERE
> (table2.accttype = 1.0)
>INTERSECT
>SELECT DISTINCT
> table3.clientid AS clientid LABEL='Client Id'
>FROM
> TEMP6 AS table3;

I guess a couple of correlated UNION selects are about our only
option for emulating INTERSECT. However, this isn't a good example
for it, since you can get the set you want without either INTERSECT
or UNION, just inner-joining to TEMP6.

Firebird doesn't have a way to LABEL output so if you want the output
to come out as "Client Id" then you'll need to use that string in
double quotes as the output alias, instead of CLIENTID.

(Though we're not using a UNION today...another point is that
unioning takes the alias from the first SELECT set; don't alias the
output fields in subsequent sets.)

In Fb 1.x.x, using AS with table aliases, though permitted by the
standard, is illegal. AFAIR, it has been allowed in Fb 2.

Anyway, try your query like this:

SELECT DISTINCT
table1.clientid AS "Client Id"
FROM
CUSTOMER1 table1
join ALLACCT table2
on table1.clientid = table2.clientid
join TEMP6 table3
on table1.clientid = table3.clientid
WHERE
table2.accttype = 1.0

./heLen