HAVING SYNTAX

[barry.marcus]

I see nothing wrong with this query, but Texis gives me the error "Field $star non-existent" (Well, I know that!!!). What am I doing wrong:

SELECT PATN_WKU, COUNT(*)
FROM PATN
WHERE PATN_ISD = '20030520'
GROUP BY PATN_WKU
HAVING COUNT(*) > 1
ORDER BY 2;

Also, can someone point me to documentation on the use of HAVING in Texis SQL.

[John]

You should rename that column and use the alias name in the query as the having is evaluated after the row has been generated, e.g.

SELECT PATN_WKU, COUNT(*) N
FROM PATN
WHERE PATN_ISD = '20030520'
GROUP BY PATN_WKU
HAVING N > 1
ORDER BY N;

The documentation is at http://www.thunderstone.com/site/texism ... aving.html which does state:

"When referring to aggregate values in the HAVING and ORDER BY clauses of a GROUP BY you must assign an alternative name to the field, and use that in the HAVING and ORDER BY clauses."

[barry.marcus]

Thanks very much, John. I didn't know about this requirement (obviously!)