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