Hi All:

I'm seeing a change in behavior of a SQL statement in VFP versions after v6. Consider the code below...

SELECT EXTRDETL.cITEM AS ProdItem, ;
		NVL(EXTRLOT.cITEM, PADR("** NO SUCH LOT", 15)) AS LotItem, ;
		EXTRDETL.cPLOTNO, ;
		EXTRMAST.dEXTRDATE, ;
		EXTRMAST.cMACHINE, ;
		EXTRMAST.cSHIFT, ;
		IIF(EXTRDETL.cITEM == EXTRLOT.cITEM, .T., .F.) AS LOTMATCH ;
	FROM EXTRMAST ;
		JOIN EXTRDETL ;
			ON EXTRMAST.iID = EXTRDETL.iID ;
		LEFT OUTER JOIN EXTRLOT ;
			ON EXTRDETL.cPLOTNO = EXTRLOT.cPLOTNO ;
	WHERE NOT EMPTY(EXTRDETL.cITEM) ;
		AND EXTRMAST.dEXTRDATE BETWEEN m.pdStartDate AND m.pdEndDate ;
		AND EXTRDETL.cITEM <> EXTRLOT.cITEM ;
INTO CURSOR _FRXQUERY ;
ORDER BY 1, 2

The expression "EXTRDETL.cITEM <> EXTRLOT.cITEM" in the WHERE clause is key here. In versions 5 and 6, if no records in EXTRLOT exist the <> operator considers .NULL. to be not equal to (ie, the left outer join will produce nulls for records that do not exist in EXTRLOT).

However, it seems in VFP 7 and 8, I can use to use EXTRDETL.cITEM <> NVL(EXTRLOT.cITEM, '** NO SUCH LOT' and I will get the records I get under v6.

I've not seen any reference to this new behavior and was looking for comments on it. I suppose the rationale is that since .NULL. means we don't know what the value is, we can't technically say whether it is or is not equal. Is that what the deal is here? Does this change make fox's SQL in line with SQL Server's? I could check, but someone else here could verify more quickly...