Bob Kline wrote:
>
> On Wed, 3 Nov 1999, Alexander Barkov wrote:
>
> >
> > Hi!
> >
> >
> > How can I refer the calculated field in HAVING clause.
> >
> > This work in MySQL:
> >
> > SELECT some_expression as field1, ...
> > FROM tablename
> > GROUP BY ...
> > HAVING field1>0;
> >
> > PostgreSQL gives error "Attribute 'field1' not found".
> >
> >
> > Are there any workarounds?
> >
> >
>
> How about HAVING some_expression > 0? (Thought your version is legal
> SQL, I believe).
>
The problem is that some_expression is big enough. I need
this query for search engine. The query depends of number of
given words. Check second field in this query:
SELECT
dict.url_id,
max(case when word IN ('word1') then 1 else 0 end)+
max(case when word IN ('word2') then 1 else 0 end)+
max(case when word IN ('word3') then 1 else 0 end) as num,
sum(intag)as rate
FROM dict,url
WHERE url.rec_id=dict.url_id
AND dict.word IN ('word1','word2','word3')
GROUP BY dict.url_id ORDER BY num DESC, rate DESC
I need to check in HAVING that calculated field 'num' is 3.
This is the sample for three words. I can duplicate big expression
for 'num' in HAVING. But the query will be huge for 10 or 15 words :-)
Strange. I cannot use 'num' in HAVING. But it works in ORDER BY.
May be I'm doing something wrong?
--
Alexander Barkov
IZHCOM, Izhevsk
email: bar@izhcom.ru | http://www.izhcom.ru
Phone: +7 (3412) 51-55-45 | Fax: +7 (3412) 78-70-10
ICQ: 7748759