Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Richard Huxton <dev@archonet.com>, pgsql-sql@postgresql.org
Subject: Concat and view - Re: create view error
Date: Mon, 7 Jul 2003 13:36:35 +0100
User-Agent: KMail/1.4.3
References: <200307071240.23936.gary.stainburn@ringways.co.uk>
	<200307071307.54935.dev@archonet.com>
In-Reply-To: <200307071307.54935.dev@archonet.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307071336.35933.gary.stainburn@ringways.co.uk>

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