Re: [GENERAL] HAVING QUESTION

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