pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: CREATE VIEW ERROR
5+ messages / 5 participants
[nested] [flat]
* Re: CREATE VIEW ERROR
@ 2003-01-23 05:00 Igor <kryltsov@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Igor @ 2003-01-23 05:00 UTC (permalink / raw)
To: pgsql-sql
I found answer.
Thank you for everyone who did not reply.
CREATE VIEW depend_view AS
SELECT depend.subfunction_id, a.subfunction_file AS x,
depend.subfunction_dep_id, b.subfunction_file AS y
FROM depend INNER JOIN subfunction a ON depend.subfunction_id =
a.subfunction_id INNER JOIN subfunction b ON depend.subfunction_dep_id =
b.subfunction_id;
"Igor" <kryltsov@yahoo.com> wrote in message
news:d418ccf3.0301181703.57244c80@posting.google.com...
> Hi,
>
> I have following SQL statement which does not report any errors:
>
> test=# SELECT a.subfunction_file, b.subfunction_file
> test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id =
> a.subfunction_id
> test-# INNER JOIN subfunction b ON depend.subfunction_dep_id =
> b.subfunction_id;
> subfunction_file | subfunction_file
> ----------------------------+----------------------------
> show_batch_2.php | search_receipt.php
> ...........
>
> But when I try to create VIEW a have following error:
>
> test=# create view depend_view as SELECT a.subfunction_file,
> b.subfunction_file
> test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id =
> a.subfunction_id
> test-# INNER JOIN subfunction b ON depend.subfunction_dep_id =
> b.subfunction_id;
>
> ERROR: CREATE TABLE: attribute "subfunction_file" duplicated
> <<<--------!!!!
>
>
> Please advise where is my mistake. I use:
>
> test=# select version();
> version
> ---------------------------------------------------------------------
> PostgreSQL 7.2.1 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
> (1 row)
>
>
> Thank you,
>
> Igor
^ permalink raw reply [nested|flat] 5+ messages in thread
* create view error
@ 2003-07-07 11:40 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 3 replies; 5+ messages in thread
From: Gary Stainburn @ 2003-07-07 11:40 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I know I'm missing something blindingly obvious, can someone point it out to
me please.
create table locos ( -- Locos table - contains details of locos
lid int4 default nextval('loco_lid_seq'::text) unique not null,
lclass int4 references lclass(lcid), -- Loco Class
lbuilt date, -- Date off-shed
lcme int4 references cme(cmid), -- Chief Mechanical Engineer
lname character varying(30), -- Name of Loco
lcomments text -- free text comments
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
table 'locos'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table lnumbers ( -- alternate loco numbers
lnid int4 not null references locos(lid),
lnumber character varying(10),
lncurrent bool,
primary key (lnid, lnumber)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
for table 'lnumbers'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create view loco_dets as
select * from locos l
left outer join
(select * from lclass) lc on lc.lcid = l.lclass
left outer join
(select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
= true
left outer join
(select * from company) c on c.coid = lc.lcompany;
ERROR: No such attribute or function ln.lnid
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: create view error
@ 2003-07-07 12:06 mila boldareva <pg@pierro.dds.nl>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: mila boldareva @ 2003-07-07 12:06 UTC (permalink / raw)
To: pgsql-sql; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>
Hi, Gary!
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
(*) (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
your subselect on line (*) does not contain lnid in the list of
selected fields.
I suspect you can also use constructions like
select * from locos l left outer join lclass lc on (lc.lcid = l.lclass)
instead of what you use:
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
in this way you automatically avoid the error that you had!
cheers,
Mila
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: create view error
@ 2003-07-07 12:07 Richard Huxton <dev@archonet.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2003-07-07 12:07 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote:
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and
^^^^^^^ ^^^^^^^
> ERROR: No such attribute or function ln.lnid
Is is this?
--
Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: create view error
@ 2003-07-07 17:05 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2003-07-07 17:05 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Mon, 7 Jul 2003, Gary Stainburn wrote:
> Hi folks,
>
> I know I'm missing something blindingly obvious, can someone point it out to
> me please.
>
> create table locos ( -- Locos table - contains details of locos
> lid int4 default nextval('loco_lid_seq'::text) unique not null,
> lclass int4 references lclass(lcid), -- Loco Class
> lbuilt date, -- Date off-shed
> lcme int4 references cme(cmid), -- Chief Mechanical Engineer
> lname character varying(30), -- Name of Loco
> lcomments text -- free text comments
> );
> NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
> table 'locos'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create table lnumbers ( -- alternate loco numbers
> lnid int4 not null references locos(lid),
> lnumber character varying(10),
> lncurrent bool,
> primary key (lnid, lnumber)
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
> for table 'lnumbers'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
^^^
select also lnid
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-07 17:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-23 05:00 Re: CREATE VIEW ERROR Igor <kryltsov@yahoo.com>
2003-07-07 11:40 create view error Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-07 12:06 ` Re: create view error mila boldareva <pg@pierro.dds.nl>
2003-07-07 12:07 ` Re: create view error Richard Huxton <dev@archonet.com>
2003-07-07 17:05 ` Re: create view error Achilleus Mantzios <achill@matrix.gatewaynet.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