pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConcat and view - Re: create view error
5+ messages / 2 participants
[nested] [flat]
* Concat and view - Re: create view error
@ 2003-07-07 12:36 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2003-07-07 12:36 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concat and view - Re: create view error
@ 2003-07-07 13:12 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2003-07-07 13:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concat and view - Re: create view error
@ 2003-07-07 14:34 Richard Huxton <dev@archonet.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2003-07-07 14:34 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote:
>
> 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
[snip]
Don't do "select *" do "select field_a,field_b..." - the * doesn't just refer
to the locos table.
--
Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concat and view - Re: create view error
@ 2003-07-07 15:10 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2003-07-07 15:10 UTC (permalink / raw)
To: pgsql-sql
On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote:
> On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote:
> > 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
>
> [snip]
>
> Don't do "select *" do "select field_a,field_b..." - the * doesn't just
> refer to the locos table.
Sorry if I didn't make myself plain enough, but I had
create view loco_dets as
select * from locos l -- includes lid which I want
left outer join
(select lnid, lnumber.......) ln on ln.lnid = l.lid
.......
The problem is that I have to have lnid in the sub-select to allow the 'on'
clause to work, but I don't want lnid to appear in the resulting view.
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Concat and view - Re: create view error
@ 2003-07-07 15:44 Richard Huxton <dev@archonet.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2003-07-07 15:44 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
On Monday 07 Jul 2003 4:10 pm, Gary Stainburn wrote:
> On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote:
> > Don't do "select *" do "select field_a,field_b..." - the * doesn't just
> > refer to the locos table.
>
> Sorry if I didn't make myself plain enough, but I had
>
> create view loco_dets as
> select * from locos l -- includes lid which I want
> left outer join
> (select lnid, lnumber.......) ln on ln.lnid = l.lid
> .......
>
> The problem is that I have to have lnid in the sub-select to allow the 'on'
> clause to work, but I don't want lnid to appear in the resulting view.
Yep, so don't do "SELECT *", list the fields you want instead. The * in that
second line applies to the rest of the query, not just the "locos" table.
There is something to be said for a format such as "SELECT * EXCEPT lnid" but
I don't think it's mentioned in any sql spec.
--
Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-07 15:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-07 12:36 Concat and view - Re: create view error Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-07 13:12 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-07 14:34 ` Richard Huxton <dev@archonet.com>
2003-07-07 15:10 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-07 15:44 ` Richard Huxton <dev@archonet.com>
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