Subject Re: [firebird-support] Why is it invalid?
Author unordained
> I Firebird says this syntaxis is wrong, but I think it is simple and
> standard sql (Isn't it?); PostgreSQL and Oracle accepts it:
> (select "TPRO"."DSCR" as "D_TPRO",cast(' ' as character(1)) as "NUME"
> from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO"
> where "PROC"."PROC"='1KZM64V704')
> union
> (select "TPRO"."DSCR" as "D_TPRO", "OTIP"."NUME" from "OTIP" left
> outer join "TPRO" on "OTIP"."TPRO"="TPRO"."TPRO"
> where "OTIP"."PROC"='1KZM64V704')
> order by "NUME"

Remove the parenthesis around your SELECTs -- Firebird already knows that the ORDER BY applies to
the result of the UNION, not to the second SELECT only.

On the other hand, Firebird expects parenethesis around SELECTs in cases where you want to treat
the SELECT as a singleton (single-field, single-row) sub-query, such as

if ((select ...) = 1) then ...
coalesce((select ...), (select ...), ...)
where ... = (select ...)

It also wants parenthesis around SELECTs for derived tables, such as

select ... from
(select ... ) as x
left join
(select ... ) as y

and for common-table-expressions, such as

with x as (select ... )
select ... from x

You might run into the following as well:
select ... from
(select ... from x
select ... from y
) as z
order by ...