>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.



Steve

I am not a HAVING fan either. The docs even confirm what you
are saying - HAVING does slow down an SQL. The only thing I
can see off the top of my head is you have a GROUP BY & HAVING
that use different work areas. The GROUP BY & HAVING should
work together.

BUt I can say with even more certainty that you are doinng
right by changing the GROUP BYs to WHEREs.

Tom