Concat and view - Re: create view error

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