From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Concat and view - Re: create view error
Date: Mon, 7 Jul 2003 14:12:35 +0100
Message-ID: <200307071401.38483.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <200307071336.35933.gary.stainburn@ringways.co.uk>
References: <200307071240.23936.gary.stainburn@ringways.co.uk>
<200307071307.54935.dev@archonet.com>
<200307071336.35933.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Concat and view - Re: create view error
In-Reply-To: <200307071401.38483.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox