Skip site navigation (1) Skip section navigation (2)

Problem using Having in a sub-query wit the Count function.

From: Steve Claverie <Steve(at)ctlno(dot)com>
To: "'pgsql-general(at)hub(dot)org'" <pgsql-general(at)hub(dot)org>, "'pgsql-interfaces(at)hub(dot)org'" <pgsql-interfaces(at)hub(dot)org>
Subject: Problem using Having in a sub-query wit the Count function.
Date: 1999-03-23 18:44:24
Message-ID: A043233669F9D111B99700A0C92376CA0F34B1@srv.ctlno.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfaces
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(at)ctlno(dot)com

pgsql-interfaces by date

Next:From: MatthewDate: 1999-03-23 18:46:10
Subject: Problem using Having in a sub-query wit the Count function.
Previous:From: José SoaresDate: 1999-03-23 15:05:58
Subject: Re: [INTERFACES] PostGreSQL --> Oracle Convertor

pgsql-general by date

Next:From: MatthewDate: 1999-03-23 18:46:10
Subject: Problem using Having in a sub-query wit the Count function.
Previous:From: MatthewDate: 1999-03-23 18:34:54
Subject: RE: [GENERAL] db out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group