Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: create view error
Date: Mon, 7 Jul 2003 12:40:23 +0100
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307071240.23936.gary.stainburn@ringways.co.uk>

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