Re: create view error

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