I have recently uncovered a problem in one of our
applications related to the SQL Having clause.
I personally prefer to use the Where clause, but my
co-developer believed that the Having clause was more
appropriate and efficient. So he used the Having clause
throughout this application. The problem is that the
Having clause is not returning the correct results.
I thought I would throw up the code to see if anyone has
seen this problem before and can provide some reasonable
explanation.

*****
CLOSE TABLES ALL

*- Simulate PVD Parent Record Position!
SELE 0
use Stack
LOCATE FOR Stack_ID="AE03" AND FAC_ID="012"

*- Load variables used in the PVD SQL statements.
lcAlias = ALIAS()
lcStack_Id = STACK.STACK_ID
lcFac_Id = STACK.FAC_ID

* SQL Example with HAVING - This Does NOT Work!!!
* No Records are returned.
SELECT * FROM G_PERMIT A, AMSPS B ;
WHERE (A.FAC_ID = B.FAC_ID AND A.P_ID = B.P_ID) ;
HAVING (B.FAC_ID = lcFac_Id AND B.STACK_ID = lcStack_Id) ;
GROUP BY A.P_ID

* SQL Example with WHERE - This Does Work!!!
* One record is returned, which is correct.
SELECT * FROM G_PERMIT A, AMSPS B ;
WHERE (A.FAC_ID = B.FAC_ID AND A.P_ID = B.P_ID) ;
AND (B.FAC_ID = lcFac_Id AND B.STACK_ID = lcStack_Id) ;
GROUP BY A.P_ID
*******

I have just started to replace all Having clauses with the
correct Where substitute. Any thoughts on this matter are
welcome.

Also, I did some preliminary benchmarking of the Having
clause vs the Where clause on large data sets and found
that Where appears more efficient. Especially, when a Where
clause was already in use.

Steve.