pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem using Having in a sub-query wit the Count function.
4+ messages / 4 participants
[nested] [flat]
* Problem using Having in a sub-query wit the Count function.
@ 1999-03-23 18:44 Steve Claverie <Steve@ctlno.com>
0 siblings, 0 replies; 4+ messages in thread
From: Steve Claverie @ 1999-03-23 18:44 UTC (permalink / raw)
To: 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
Hey,
We are trying to execute a query that has several sub-queries
embedded in it. Below is a snippet of the sql code.
"Select ordnum from ordinace where dept='Finance' and ordnum in (Select
ordnum from squareview where square='A') and ordnum in (select ordnum
from keywordview where keyword='Parade' or keyword='Public Gathering'
group by ordnum having count(ordnum) =2)"
The two tables in the sub-queries (squareview and keywordview) or views
created between two tables.
There are roughly about 20000 records in the keywordview view.
When we execute the query, failing at the keywordview subquery, saying
the aggregate function in the having clause must appear on the right
side. (?) When we take the having clause out, and strictly have the
group by, it takes 30secs to 3mins. to return with the valid recordset.
The funny thing is, as a stand alone query on it's own, the keywordview
query works fine. It's very quick and has no problem with the having
clause.
I was wondering if anyone else has either had this problme using
aggregate functions with the having clause in a subquery, or could
anyone give me any information of successfully executing something
similar to this.
Any infomation would be appreciated.
Thanks in advance,
Steve
steve@ctlno.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Problem using Having in a sub-query wit the Count function.
@ 1999-03-23 18:46 Matthew <matt@ctlno.com>
1999-03-24 13:25 ` Re: [INTERFACES] Problem using Having in a sub-query wit the Count function. Ordini <sferac@bo.nettuno.it>
1999-03-24 14:54 ` Re: [INTERFACES] Problem using Having in a sub-query wit the Count function. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Matthew @ 1999-03-23 18:46 UTC (permalink / raw)
To: 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
> We are trying to execute a query that has several sub-queries embedded
> in it. Below is a snippet of the sql code.
>
> "Select ordnum from ordinace where dept='Finance' and ordnum in
> (Select ordnum from squareview where square='A') and ordnum in (select
> ordnum from keywordview where keyword='Parade' or keyword='Public
> Gathering' group by ordnum having count(ordnum) =2)"
>
> The two tables in the sub-queries (squareview and keywordview) or
> views created between two tables.
> There are roughly about 20000 records in the keywordview view.
>
> When we execute the query, failing at the keywordview subquery, saying
> the aggregate function in the having clause must appear on the right
> side. (?) When we take the having clause out, and strictly have the
> group by, it takes 30secs to 3mins. to return with the valid
> recordset.
>
> The funny thing is, as a stand alone query on it's own, the
> keywordview query works fine. It's very quick and has no problem with
> the having clause.
>
> I was wondering if anyone else has either had this problme using
> aggregate functions with the having clause in a subquery, or could
> anyone give me any information of successfully executing something
> similar to this.
>
> Any infomation would be appreciated.
>
> Thanks in advance,
>
> Steve
> steve@ctlno.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.
1999-03-23 18:46 Problem using Having in a sub-query wit the Count function. Matthew <matt@ctlno.com>
@ 1999-03-24 13:25 ` Ordini <sferac@bo.nettuno.it>
1 sibling, 0 replies; 4+ messages in thread
From: Ordini @ 1999-03-24 13:25 UTC (permalink / raw)
To: Matthew <matt@ctlno.com>; +Cc: 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
bug: HAVING IN SUBQUERIES
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is a known bug.
Jose'
> > We are trying to execute a query that has several sub-queries embedded
> > in it. Below is a snippet of the sql code.
> >
> > "Select ordnum from ordinace where dept='Finance' and ordnum in
> > (Select ordnum from squareview where square='A') and ordnum in (select
> > ordnum from keywordview where keyword='Parade' or keyword='Public
> > Gathering' group by ordnum having count(ordnum) =2)"
> >
> > The two tables in the sub-queries (squareview and keywordview) or
> > views created between two tables.
> > There are roughly about 20000 records in the keywordview view.
> >
> > When we execute the query, failing at the keywordview subquery, saying
> > the aggregate function in the having clause must appear on the right
> > side. (?) When we take the having clause out, and strictly have the
> > group by, it takes 30secs to 3mins. to return with the valid
> > recordset.
> >
> > The funny thing is, as a stand alone query on it's own, the
> > keywordview query works fine. It's very quick and has no problem with
> > the having clause.
> >
> > I was wondering if anyone else has either had this problme using
> > aggregate functions with the having clause in a subquery, or could
> > anyone give me any information of successfully executing something
> > similar to this.
> >
> > Any infomation would be appreciated.
> >
> > Thanks in advance,
> >
> > Steve
> > steve@ctlno.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.
1999-03-23 18:46 Problem using Having in a sub-query wit the Count function. Matthew <matt@ctlno.com>
@ 1999-03-24 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-03-24 14:54 UTC (permalink / raw)
To: Matthew <matt@ctlno.com>; +Cc: 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
Matthew <matt@ctlno.com> writes:
>> "Select ordnum from ordinace where dept='Finance' and ordnum in
>> (Select ordnum from squareview where square='A') and ordnum in (select
>> ordnum from keywordview where keyword='Parade' or keyword='Public
>> Gathering' group by ordnum having count(ordnum) =2)"
I wonder whether the parser could be getting confused by the multiple
distinct uses of the same name "ordnum" in this query? In other words,
maybe you'd have better luck if the inner queries read something like
select
k.ordnum from keywordview k where k.keyword='Parade' or k.keyword='Public
Gathering' group by k.ordnum having count(k.ordnum) =2
Without that, it might be thinking that count(ordnum) refers to the
ordnum in the outer select.
If that is it, it's probably a bug, but I'm not sure what the standard
says about how to interpret ambiguous names in this context...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-03-24 14:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-23 18:44 Problem using Having in a sub-query wit the Count function. Steve Claverie <Steve@ctlno.com>
1999-03-23 18:46 Problem using Having in a sub-query wit the Count function. Matthew <matt@ctlno.com>
1999-03-24 13:25 ` Ordini <sferac@bo.nettuno.it>
1999-03-24 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
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