Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f22G0Zx39531 for ; Fri, 2 Mar 2001 11:00:35 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f22Dxrx12231 for ; Fri, 2 Mar 2001 08:59:53 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f22DkGM98134 for pgsql-sql@postgresql.org; Fri, 2 Mar 2001 08:46:16 -0500 (EST) (envelope-from news) From: Martin Lillepuu X-Newsgroups: comp.databases.postgresql.sql Subject: union & subqueries Date: Fri, 02 Mar 2001 15:42:43 +0200 Lines: 57 Message-ID: <3A9FA353.EE681533@lillepuu.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.ut.ee X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200103/24 X-Sequence-Number: 1666 hello, when I run following sql in 2 separate queries, they work fine. but when used as one beg statement with UNION, I get following error: join_references: variable not in subplan target lists If I remove subqueries, they also work fine. I'm currently using postgres 7.0.2. would upgrading to 7.0.3 or 7.1 beta fix this? or is there a workaround for current version? Query is supposed to get all AP/AR transactions with related customer/vendor records and transactions expense/income account name (with subquery). ---- 8< ---- 8< ---- SELECT ar.id, ar.invnumber as source, ar.notes, customer.name, customer.addr1, customer.addr2, customer.addr3, acc_trans.transdate, ar.invoice, acc_trans.amount, ( SELECT C.description FROM chart C, acc_trans AT WHERE AT.trans_id = ar.id AND AT.amount > 0 AND AT.amount=-ar.amount AND C.accno=AT.accno ) as desc, 'ar' as type FROM ar, acc_trans WHERE acc_trans.accno IN (1061,1065) AND acc_trans.trans_id = ar.id AND ar.customer = customer.id UNION SELECT ap.id, ap.ordnumber as source, ap.notes, vendor.name, vendor.addr1, vendor.addr2, vendor.addr3, acc_trans.transdate, ap.invoice, acc_trans.amount, ( SELECT C.description FROM chart C, acc_trans AT WHERE AT.trans_id = ap.id AND AT.amount < 0 AND AT.amount=-ap.amount AND C.accno=AT.accno ) as desc, 'ap' as type FROM ap, acc_trans WHERE acc_trans.accno IN (1061,1065) AND acc_trans.trans_id = ap.id AND ap.vendor = vendor.id ORDER BY transdate ---- 8< ---- 8< ---- -- Martin Lillepuu | E-mail: martin@lillepuu.com | GSM: 051 56 450