HAVING SYNTAX

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

HAVING SYNTAX

Post by 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.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

HAVING SYNTAX

Post by 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."
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

HAVING SYNTAX

Post by barry.marcus »

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