Re: 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