pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConverting query to view - duplicate fields
3+ messages / 2 participants
[nested] [flat]
* Converting query to view - duplicate fields
@ 2004-03-09 22:40 Richard Grosse <lists@keep-trying.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Grosse @ 2004-03-09 22:40 UTC (permalink / raw)
To: pgsql-sql
Hello,
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)
So how to a refer to the same table more than once and
get the data without getting the above error. (Basically it
is looking up different addresses depending on the codes
stored in mhdespatch table). Can I do this with a view, or
do I have to create several views and link them together.
Thanks
Richard
Postgresql 7.4.1,SuSE 8.1
Query Begins ==>
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;
<=== Query Ends
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Converting query to view - duplicate fields
@ 2004-03-10 02:17 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Richard Grosse <lists@keep-trying.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2004-03-10 02:17 UTC (permalink / raw)
To: Richard Grosse <lists@keep-trying.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Converting query to view - duplicate fields - solved
@ 2004-03-10 22:51 Richard Grosse <lists@keep-trying.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Grosse @ 2004-03-10 22:51 UTC (permalink / raw)
To: pgsql-sql
At 02:17 10/03/2004, you wrote:
>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).
Great just what I needed to know. Works fine now :-)
Thanks
Richard
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-03-10 22:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 22:40 Converting query to view - duplicate fields Richard Grosse <lists@keep-trying.com>
2004-03-10 02:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-10 22:51 ` Re: Converting query to view - duplicate fields - solved Richard Grosse <lists@keep-trying.com>
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