UNION problem

On Tuesday 04 Feb 2003 6:08 am, Jean-Christian Imbeault wrote:

I have the following two separate QL queires which work fine alone. But
if I tried and UNION them I get an error which doesn't seem to make
sense, "Attribute not found ...". Can someone spot and point out out my
mistake/error?

TAL=# SELECT prod_id FROM invoice_li, products, movies WHERE
prod_id=products.id AND movies.id=products.id AND received AND ship_now
AND not cancelled AND invoice_id not in (select id from invoices where
cancelled) UNION SELECT prod_id FROM invoice_li, products, movies WHERE
invoice_id not in (select invoice_id from invoice_li where cancelled OR
not received OR shipped) AND prod_id=products.id AND
movies.id=products.id AND received AND ship_now AND not cancelled GROUP
BY prod_id, prod_type_id, maker_id, maker_prod_code, label_id ORDER BY
prod_type_id, maker_id, maker_prod_code, label_id
TAL-# ;
ERROR: Attribute "prod_type_id" not found

Since the two queries work separately, I'd guess PG is trying to locate
prod_type_id via a different route when unioned. What happens if you qualify
all the column-names?

Something like:
select min(f.a) from foo f group by f.b UNION select min(f2.a) FROM foo f2
group by f2.b;

--
Richard Huxton

Richard Huxton <dev@archonet.com> writes:

Since the two queries work separately, I'd guess PG is trying to locate
prod_type_id via a different route when unioned. What happens if you qualify
all the column-names?

ORDER BY applied to a UNION result can only order by the column names
visible in the UNION result. Consider for example

SELECT x1 AS a FROM foo
UNION
SELECT y1 AS a FROM bar
ORDER BY ???

where foo and bar have no column names in common. The *only* thing
that's sensible to order by is "a" --- and no qualification, mind you.
Anything else you might try to order by is not available in one or the
other arm of the UNION.

The SQL92 spec is very rigid about this, and so is Postgres. You could
imagine ordering by, say, UPPER(a), but we don't support that extension
at present (unlike the situation for ORDER BY in non-UNION queries,
where we're quite lax).

regards, tom lane