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
