Behavior Changes in VFP 9.0: _TALLY

One of the SQL data engine changes introduced in VFP 9.0 affects the value of _TALLY returned after certain kinds of SQL SELECT statements. This change is not documented in the "Changes in Functionality for the Current Release" topic in the VFP 9.0 Help file, and may therefore have escaped the attention of some VFP developers. It is documented under the "SET ENGINEBEHAVIOR Command" topic, however.

The change is this: In previous versions of VFP, _TALLY returns zero when an aggregate function such as SUM() is used and there are no matching records, while in VFP 9.0, when an aggregate function such as SUM() is used without a GROUP BY clause and there are no matching records, _TALLY returns 1 and the result set contains one record with a null value for the aggregate function.

This change breaks code that tests the value of _TALLY to determine if any matching records were found.

To illustrate this, create a table of ten records with integer values and an ID of "foo".

CREATE TABLE myTable ( cID c(10), nValue I)
LOCAL lni
FOR lni = 1 TO 10
 INSERT INTO myTable ( cID, nValue) VALUES ( "foo", lni)
ENDFOR

Now perform a SELECT statement to SUM() the nValue of all records with an cID of "bar". Of course, there are no matching records. Under VFP 7.0 and VFP 8.0, the test for _tally = 0 returns true so Procedure A is performed.

SET ENGINEBEHAVIOR 70
SELECT SUM( nValue) ;
 FROM myTable ;
 WHERE cID = "bar" ;
 INTO CURSOR csrBehavior70
IF _tally = 0        && true
  DO Procedure_A     && Procedure_A is performed
ELSE
  DO Procedure_B
ENDIF

SET ENGINEBEHAVIOR 80
SELECT SUM( nValue) ;
 FROM myTable ;
 WHERE cID = "bar" ;
 INTO CURSOR csrBehavior80
IF _tally = 0        && true
  DO Procedure_A     && Procedure_A is performed
ELSE
  DO Procedure_B
ENDIF

Now perform the same test in VFP 9.0. With no other changes to the code, the test for _tally = 0 returns false so Procedure B is performed instead of Procedure A.

SET ENGINEBEHAVIOR 90
SELECT SUM( nValue) ;
 FROM myTable ;
 WHERE cID = "bar" ;
 INTO CURSOR csrBehavior90
IF _tally = 0        && false
  DO Procedure_A
ELSE
  DO Procedure_B     && Procedure_B is performed
ENDIF

The null value of the aggregate function can be seen in the one record in the result set:

SELECT csrBehavior90
?csrBehavior90.sum_nValue   && .NULL.

Although the best solution is probably to stop relying on _tally altogether, for this as well as for other reasons, one workaround in VFP 9.0 is to include a GROUP BY clause in the SELECT statement, which restores the result obtained in VFP 7.0 and 8.0.

SET ENGINEBEHAVIOR 90
SELECT cID, SUM( nValue) ;
 FROM myTable ;
 WHERE cID = "bar" ;
 INTO CURSOR csrBehavior90 ;
 GROUP BY cID
IF _tally = 0        && true
  DO Procedure_A     && Procedure_A is performed
ELSE
  DO Procedure_B
ENDIF

The changes to the SQL engine implemented by the VFP team in versions 8.0 and 9.0 have helped bring VFP's SQL implementation closer in line with SQL standards. This is a good thing, but watch out for changes like this one that might break existing code.