X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 8167CB42168
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  7 Jul 2003 12:06:47 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 41930-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  7 Jul 2003 09:06:33 -0300 (ADT)
Received: from utrhcs.cs.utwente.nl (utrhcs.cs.utwente.nl [130.89.10.247])
	by svr1.postgresql.org (Postfix) with ESMTP id D7C00B42167
	for <pgsql-sql@postgresql.org>; Mon,  7 Jul 2003 09:06:32 -0300 (ADT)
Received: from zeus.cs.utwente.nl (zeus.cs.utwente.nl [130.89.10.12])
	by utrhcs.cs.utwente.nl (8.12.9/8.12.9) with ESMTP id h67C6UCf001524;
	Mon, 7 Jul 2003 14:06:30 +0200 (MET DST)
Received: from utip095.dynamic.cs.utwente.nl (utip095 [130.89.11.76])
	by zeus.cs.utwente.nl (8.12.9/8.12.9) with ESMTP id h67C6Sl2019260;
	Mon, 7 Jul 2003 14:06:28 +0200 (MEST)
Date: Mon, 7 Jul 2003 14:06:29 +0200
From: mila boldareva <pg@pierro.dds.nl>
X-Mailer: The Bat! (v1.62r) Educational
X-Priority: 3 (Normal)
Message-ID: <1717486813.20030707140629@pierro.dds.nl>
To: pgsql-sql@postgresql.org
Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>
Subject: Re: create view error
In-Reply-To: <200307071240.23936.gary.stainburn@ringways.co.uk>
References: <200307071240.23936.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS-perl11-milter (http://amavis.org/)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/117
X-Sequence-Number: 14231

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