From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Concat and view - Re: create view error
Date: Mon, 7 Jul 2003 13:36:35 +0100
Message-ID: <200307071336.35933.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <200307071307.54935.dev@archonet.com>
References: <200307071240.23936.gary.stainburn@ringways.co.uk>
<200307071307.54935.dev@archonet.com>
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?
Also, using examples from this list, I've created a concat function and
aggregate so that I can convert a number of rows to a comma delimited string.
I can then use this in a select as shown below, but what I can't work out is
how to put this into my join.
I want to include the second of the two selects shown below (the one with
'lncurrent = true' where clause) into my view (shown at bottom).
I can't work out where to put the where and group by clauses.
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers group by
lnid;
lnid | lnalternate
------+--------------
1 | 29
2 | 2392,65894
3 | 4277
4 | 80135
5 | 30926,926
6 | 45212
7 | 44767
8 | 60532
9 | 75014
10 | 75029
11 | 60007
12 | 25 278,D7628
13 | 08850,4518
14 | 62005,62012
15 | 24 061,D5061
16 | 45337
17 | 6619
18 | 64360,901
19 | 5
20 | 825
21 | 45157
22 | 76079
23 | 4771,60800
24 | 55019,D9019
25 | D9009
26 | 08556,D3723
(26 rows)
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers where
lncurrent = false group by lnid;
lnid | lnalternate
------+-------------
2 | 2392
5 | 926
12 | 25 278
13 | 08850
14 | 62012
18 | 64360
23 | 4771
24 | D9019
26 | D3723
(9 rows)
nymr=#
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, concat(lnumber) as lnalternate, lncurrent from lnumbers
) na on na.lnid = l.lid and na.lncurrent = false
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, dev@archonet.com
Subject: Re: Concat and view - Re: create view error
In-Reply-To: <200307071336.35933.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