X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A2C5ED1B530 for ; Wed, 10 Mar 2004 02:17:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 41209-05 for ; Tue, 9 Mar 2004 22:17:05 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id CBBB3D1CAE3 for ; Tue, 9 Mar 2004 22:17:01 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id B65D03519F; Tue, 9 Mar 2004 18:17:04 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B4C70350FC; Tue, 9 Mar 2004 18:17:04 -0800 (PST) Date: Tue, 9 Mar 2004 18:17:04 -0800 (PST) From: Stephan Szabo To: Richard Grosse Cc: pgsql-sql@postgresql.org Subject: Re: Converting query to view - duplicate fields In-Reply-To: <6.0.0.22.0.20040309223517.0351e328@192.168.5.1> Message-ID: <20040309181341.S12789@megazone.bigpanda.com> References: <6.0.0.22.0.20040309223517.0351e328@192.168.5.1> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/96 X-Sequence-Number: 17195 On Tue, 9 Mar 2004, Richard Grosse wrote: > Trying to convert the query below to a view. The problem is > despite it working as a query when trying to save it as a > view the database returns the error that the field tablealias.cmpname > is duplicated. (Which it is and has to be) You're going to need to use column aliases to alias (for example) tbldeliver.cmpname, tblsender.cmpname, tblhaulier.cmpname to different aliases in the view, perhaps "tbldeliver.cmpname" "tblsender.cmpname" and "tblhaulier.cmpname" (note the double quotes). > CREATE OR REPLACE VIEW public.test AS > SELECT mhdespatch.gdn, mhdespatch.date, mhdespatch.remarks, > mhdespatch.sp_instruct1, > mhdespatch.sp_instruct2, mhdespatch.sp_instruct3, tbldelivery.cmpname, > tbldelivery.adds1, tbldelivery.adds2, tbldelivery.adds3, tbldelivery.town, > tbldelivery.county, tbldelivery.pcode, tbldelivery.country, tbldelivery.tel1, > mhdespatch.del_ref, tblsender.cmpname, mhdespatch.send_ref, > tblhaulier.cmpname, > mhdespatch.haul_reg, mhdespatch.cov, tblhaulier.tel1 > FROM mhdespatch > INNER JOIN tblfmna AS tblhaulier ON mhdespatch.haul_code = tblhaulier.code > INNER JOIN tblfmna AS tblsender ON mhdespatch.send_code = tblsender.code > INNER JOIN tblfmna AS tbldelivery ON mhdespatch.del_code = tbldelivery.code;