Problem using Having in a sub-query wit the Count function.
Show quoted text
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
bug: HAVING IN SUBQUERIES
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is a known bug.
Jose'
Show quoted text
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
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
Import Notes
Reply to msg id not found: YourmessageofTue23Mar1999124610-0600A043233669F9D111B99700A0C92376CA0DD6F1@srv.ctlno.com | Resolved by subject fallback