pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub-sel/group problem
5+ messages / 4 participants
[nested] [flat]
* sub-sel/group problem
@ 2003-08-11 10:24 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 3 replies; 5+ messages in thread
From: Gary Stainburn @ 2003-08-11 10:24 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I don;t know if it's cos it's Monday or what, but I can't see what's wrong
here.
I've got two tables, turns which holds a turn number, a task, and where
appropriate a link to a loco. The select below works but only shows those
tasks where a loco is involved.:
select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task
from rides r, loco_dets l where r.rlid = l.lid group by rtid;
rtid | task
------+---------------------------------------------
5 | G on 60007
6 | A on 75014, C on 75014, A on 75029, C on 75029
7 | C on 4277, A on 44767, C on 44767
8 | A on 30926, C on 30926, G on 60532
9 | A on 30926, C on 30926, A on 75014, C on 75014
10 | F on 2392, F on 75029, L on 75029
11 | A on 44767, C on 44767, A on 75029
However, when I tried to change this to using an outer join I'm getting stuck.
Can anyone see my stumbling point, which I think is to do with the condition
part of the case statement. Do I need to do that in a sub-select first or is
there an alternative?
=# select r.rtid,
-# case when r.rlid > 0 then
-# concat(r.rcid::text || ' on ' || l.lnumber::text)
-# else
-# r.rcid::text
-# end as task
-# from rides r
-# left outer join loco_dets l on r.rlid = l.lid
-# group by rtid
-# order by rtid
-# ;
ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function
=#
--
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: sub-sel/group problem
@ 2003-08-11 10:38 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-08-11 10:38 UTC (permalink / raw)
To: pgsql-sql; gary.stainburn@ringways.co.uk
>
> select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as
task
> from rides r, loco_dets l where r.rlid = l.lid group by rtid;
>
Looks like another implicit FROM clause mystery.
Try
... GROUP BY r.rtid ;
The same goes for the JOIN.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sub-sel/group problem
@ 2003-08-11 10:38 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2003-08-11 10:38 UTC (permalink / raw)
To: pgsql-sql
On Monday 11 August 2003 11:24 am, Gary Stainburn wrote:
> Hi folks,
>
> I don;t know if it's cos it's Monday or what, but I can't see what's wrong
> here.
>
> I've got two tables, turns which holds a turn number, a task, and where
> appropriate a link to a loco. The select below works but only shows those
> tasks where a loco is involved.:
>
[snip]
Having re-read my email and had another go, I've opted for the sub-select
approach, and come up with:
select rtid, concat(task) from
(select rtid,
case when r.rlid > 0 then
r.rcid::text || ' on ' || l.lnumber::text
else
r.rcid::text
end as task
from rides r
left outer join loco_dets l on r.rlid = l.lid) r
group by rtid
order by rtid
;
Can anyone see any problems with this, or come up with a better approach?
--
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: sub-sel/group problem
@ 2003-08-11 13:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-08-11 13:29 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Mon, 11 Aug 2003, Gary Stainburn wrote:
> I don;t know if it's cos it's Monday or what, but I can't see what's wrong
> here.
>
> I've got two tables, turns which holds a turn number, a task, and where
> appropriate a link to a loco. The select below works but only shows those
> tasks where a loco is involved.:
>
> select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task
> from rides r, loco_dets l where r.rlid = l.lid group by rtid;
I assume concat is an aggregate function?
>
> rtid | task
> ------+---------------------------------------------
> 5 | G on 60007
> 6 | A on 75014, C on 75014, A on 75029, C on 75029
> 7 | C on 4277, A on 44767, C on 44767
> 8 | A on 30926, C on 30926, G on 60532
> 9 | A on 30926, C on 30926, A on 75014, C on 75014
> 10 | F on 2392, F on 75029, L on 75029
> 11 | A on 44767, C on 44767, A on 75029
>
> However, when I tried to change this to using an outer join I'm getting stuck.
> Can anyone see my stumbling point, which I think is to do with the condition
> part of the case statement. Do I need to do that in a sub-select first or is
> there an alternative?
>
> =# select r.rtid,
> -# case when r.rlid > 0 then
> -# concat(r.rcid::text || ' on ' || l.lnumber::text)
> -# else
> -# r.rcid::text
> -# end as task
> -# from rides r
> -# left outer join loco_dets l on r.rlid = l.lid
> -# group by rtid
> -# order by rtid
> -# ;
> ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function
Something like this might work:
select r.rtid,
concat(r.rcid::text || coalesce( ' on ' || l.lnumber::text,''))
as task
from rides r
left outer join loco_dets l on r.rlid=l.lid
group by rtid
order by rtid;
IIRC, SQL99 has a complicated way of defining dependant columns for group
by but SQL92 only allows you to use the columns that are grouped outside
of an aggregate. In general, if r.rlid is >0 in some cases and <0 in
others for a particular group, what would the query do? You might be able
to show that it doesn't happen, but the system isn't quite that bright. ;)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sub-sel/group problem
@ 2003-08-11 13:49 Tomasz Myrta <jasiek@klaster.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2003-08-11 13:49 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
> Hi folks,
>
> I don;t know if it's cos it's Monday or what, but I can't see what's wrong
> here.
>
> I've got two tables, turns which holds a turn number, a task, and where
> appropriate a link to a loco. The select below works but only shows those
> tasks where a loco is involved.:
>
> select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task
> from rides r, loco_dets l where r.rlid = l.lid group by rtid;
>
> rtid | task
> ------+---------------------------------------------
> 5 | G on 60007
> 6 | A on 75014, C on 75014, A on 75029, C on 75029
> 7 | C on 4277, A on 44767, C on 44767
> 8 | A on 30926, C on 30926, G on 60532
> 9 | A on 30926, C on 30926, A on 75014, C on 75014
> 10 | F on 2392, F on 75029, L on 75029
> 11 | A on 44767, C on 44767, A on 75029
>
> However, when I tried to change this to using an outer join I'm getting stuck.
> Can anyone see my stumbling point, which I think is to do with the condition
> part of the case statement. Do I need to do that in a sub-select first or is
> there an alternative?
>
> =# select r.rtid,
> -# case when r.rlid > 0 then
> -# concat(r.rcid::text || ' on ' || l.lnumber::text)
> -# else
> -# r.rcid::text
> -# end as task
> -# from rides r
> -# left outer join loco_dets l on r.rlid = l.lid
> -# group by rtid
> -# order by rtid
> -# ;
> ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function
> =#
Watch out.
When using outer joins you get NULL values.
some_text || NULL = NULL (always)
Try this:
select r.rtid,
concat(r.rcid::text || coalesce(' on ' || l.lnumber::text,'')
as task
from rides r
left outer join loco_dets l on r.rlid = l.lid
group by r.rtid
order by r.rtid
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-11 13:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-11 10:24 sub-sel/group problem Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-08-11 10:38 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-08-11 13:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-11 13:49 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-11 10:38 Re: sub-sel/group problem Christoph Haller <ch@rodos.fzk.de>
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