Thread: Problem using Having in a sub-query wit the Count function.
> 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' > > 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