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