--- In ib-support@y..., "Ann W. Harrison" <aharrison@i...> wrote:

> A customer converting from Oracle has a (horrible,
> massive, badly performing) query that includes
> something like this:
>
> select x from foo
> where exists (select 1 from foo where x > 10
> union
> select 1 from foo where x < 10)
>
> (I know that's an idiotic query - it's the syntax
> I'm worried about at the moment.)
>
> The DSQL parser spits up when it encounters the
> union, just as if it thought that you couldn't
> have a UNION clause in a subquery. My recent
> web surf has failed to enlighten me, and I'm quite
> sure I'm confusing the rules about updateable views
> with the rules about subqueries.
>
> Does anyone know whether union clauses are allowed
> in subqueries according to the SQL-99 or SQL-92
> standards, any level? Not that it's going to do any
> great good. Even if we took the syntax, we'd more
> than likely create an access plan that would take
> months to execute.

[Ahh, then I've got just about enough time to check SQL92 rules :-) ]

From the SQL92 Leveling rules (conformance bumph) for subclause 7.11
<scalar subquery>, <row subquery>, and <table subquery>:

"2) The following restrictions apply for Entry SQL in addition to any
Intermediate SQL restrictions:
<...>
b) The <query expression> contained in a <subquery> shall be a <query
specification>."

<query specification> does not include UNION ... <query expression>
does include UNION.

Not that it's going to do any great good, but hope that helps ...

db