>I have the following select:
>
>SELECT c_Masters.iMasterStmt,COUNT(*) as icnt ;
> FROM c_Masters ;
> GROUP BY 1 ;
> HAVING iCnt>1 ;
> INTO CURSOR c_PartialClosedMastersTemp
>
>The having clause is not filtering. Here are the first few lines of my results:
>
>Imasterstmt Icnt
>133088 1
>133090 1
>133093 1
>133095 1
>133096 1
>133097 1
>133101 1
>133103 1
>133105 1
>
>I've seen this before. Am I doing something wrong, or is this a bug?
>
>FYI - c_Masters is also a cursor, based on the following SQL:
>
>SELECT stmts.iMasterStmt,stmts.lClosed,COUNT(*) as icnt ;
> FROM stmts ;
> WHERE stmts.iMasterStmt # 0 ;
> GROUP BY 1,2 ;
> INTO CURSOR c_Masters readwrite
>
>STMTS is a VFP table.
>
>Thanks
>
>David
David,
It's not bug VFP wise but a bug in your coding.
You're first doing a select into cursor and then asking another set having 1 from first result.
Consider this:
1,f
1,f
1,f
2,t
2,t
First query would result:
1,f,3
2,t,2
Seocnd query would result:
1,1
2,1
Because while you're selecting the second you're asking iCnt field > 1. They are both > 1 and their cnt(*) is 1.
In VFP9 you wouldn't be able to run like that (unless set enginebehavior).
Probably you meant:
SELECT c_Masters.iMasterStmt,COUNT(*) as icnt ;
FROM c_Masters ;
GROUP BY 1 ;
HAVING cnt(*)>1 ;
INTO CURSOR c_PartialClosedMastersTemp
Cetin