From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Richard Grosse <lists@keep-trying.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Converting query to view - duplicate fields
Date: Tue, 9 Mar 2004 18:17:04 -0800 (PST)
Message-ID: <20040309181341.S12789@megazone.bigpanda.com> (raw)
In-Reply-To: <6.0.0.22.0.20040309223517.0351e328@192.168.5.1>
References: <6.0.0.22.0.20040309223517.0351e328@192.168.5.1>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, lists@keep-trying.com
Subject: Re: Converting query to view - duplicate fields
In-Reply-To: <20040309181341.S12789@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox