Subject Re: [firebird-support] Nested sub-queries
Author Helen Borrie
At 10:32 AM 15/07/2003 +0100, you wrote:
>Hello All,
>I'm trying to execute Nested Query (i.e. a select clause in the from
>clause of another query).
>But it is giving me error. The same query is running on Oracle and MS
>Access. I tried adding parantheses as well.
>Is it possible to execute such queries.

No. In Firebird, you can use embedded queries but their use is either to
extract a single ("scalar" = 1 column from 1 row) value for output AS a
column, or to test a predicate. These subqueries can be correlated or

So you can do (correlated)
select a.col1, a.col2,
(select b.colx from tableb b where b.keyvalue = a.keyvalue
where...) as colx,
from tablea a

select col1, col2,
(select colx from tableb where keyvalue = aconstant
where...) as colx,
from tablea

And you can use a correlated or non-correlated subquery as the argument to
the existential predicates EXISTS(..), IN(..), SOME(..), SINGULAR(..),
ANY(..) and (with care) NOT (EXISTS | IN | SINGULAR)).