Re: Concat and view - Re: create view error

On Monday 07 Jul 2003 1:36 pm, you wrote:
> On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote:
> > 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?
>
> Yup, thanks to both of you for this answer.
>
> Is there any way to do this so that lnid is not visible in the resulting
> view?
>

I've managed to get the view I wanted by using sub-selects - as shown below, 
but I now have the 'lid' field showing in the resulting view three times (as 
lid, lnid and lnaid).  How can I remove lnid and lnaid from the result?

create view loco_dets as select * from locos l     left outer join         lclass lc on lc.lcid = l.lclass     left
outerjoin       lnumbers n on n.lnid = l.lid and n.lncurrent = true     left outer join       (select lnid as lnaid,
concat(lnumber)as lnalternate from   (select lnid, lnumber from lnumbers where lncurrent = false order by lnid, 
 
lnumber) alt  group by lnaid) na on na.lnaid = l.lid      left outer join        company c on c.coid = lc.lcompany;


-- 
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