Re: [GENERAL] HAVING QUESTION

On Wed, 3 Nov 1999, Alexander Barkov wrote:

> 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?  (Though 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?
>

It appears that although some implementations support the syntax you're
trying to use, SQL92 (and apparently PostgreSQL) doesn't.  What SQL92
*does* support, would be:

  SELECT url_id,
         num,
         rate
    FROM (SELECT ... FROM ... WHERE ...GROUP BY ...) AS tmptab
   WHERE num = 3
ORDER BY rate DESC

If the DBMS doesn't support this either, then you could resort to
creating a temporary table.

--
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com