Обсуждение: UNION problem

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?

Query 1:

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);

Query 2:

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;

UNION:

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

Thanks!

Jc

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