>>>>Hi Jay
>>>>
>>>>>In the SQL below, I'm using TransHist.iTransact to determine whether I want a result record or not. What I really need is to know if the exact record does not exist in TransHist, not just by using the iTransact field value. The same fields exist in the crsTestAcctContiUniCont cursor and the TransHist table. How would I do this?
>>>>>
>>>>>
>>>>>

>>>>>SELECT * ;
>>>>>	FROM crsTestAcctContiUniCont;
>>>>>	WHERE crsTestAcctContiUniCont.iTransact NOT IN (SELECT iTransact FROM TransHist) ;
>>>>>	INTO CURSOR crsUpdate
>>>>>

>>>>
>>>>
>>>>If I'm reading you right, this will give all crsTestAcctContiUniCont records that have a match in TransHist (using the iTransact) but differ in any one of the other fields. It will also union in all the crsTestAcctContiUniCont records that have no match in TransHist. If I remember correctly the ORs will stop processing as soon as any one of them is true.
>>>>
>>>>

>>>>select ;
>>>>    TA.* ;
>>>>  from ;
>>>>    crsTestAcctContiUniCont TA ;
>>>>  inner join ;
>>>>    TransHist TH ON ;
>>>>      TA.iTransact = TH.iTransact ;
>>>>      AND (TA.Field1 # TH.Field1 ;
>>>>       OR TA.Field2 # TH.Field2 ;
>>>>       OR TA.Field3 # TH.Field3 ... ) ;
>>>>union ;
>>>>  select ;
>>>>    TA.* ;
>>>>  from ;
>>>>    crsTestAcctContiUniCont TA ;
>>>>  where ;
>>>>    TA.iTransact NOT IN (SELECT TH.iTransact FROM TransHist TH)
>>>>

>>>
>>>Mike,
>>>Unfortunately OR/AND doesn't stop in SQL (hope I'm wrong and ith has changed). A "join on ..." is likely to C...5 with too many fields to compare.
>>>Cetin
>>
>>It change in VFP9
>
>Fabio,
>Are you sure?

Is, it is changed, but not everywhere.

>

>CLEAR
>CREATE CURSOR test1 (v1 i)
>CREATE CURSOR test2 (v1 i)
>FOR ix=1 TO 5
>	INSERT INTO test1 VALUES (m.ix)
>	INSERT INTO test2 VALUES (m.ix)
>ENDFOR
>
>SELECT * ;
> FROM test1 t1 ;
>  left JOIN test2 t2 ON (t1.v1 = t2.v1 OR udf())
>
>FUNCTION udf
>? "Should never be called"
>

>
>PS: Correction
>SELECT * ;
> FROM test1 t1 ;
> left JOIN test2 t2 ON (t1.v1 = t2.v1 OR udf(t1.v1,t2.v1))
>
>FUNCTION udf
>LPARAMETERS tn1,tn2
>? tn1,tn2
>
>It's called once while it shouldn't.
>
>Cetin

VFP
- stop the evaluation when a single field expression return .t.
- continue when a cross multitable expression return .t.

Of course this have not sense,
but i think that VFPT they are gotten tired,
and they have not gone to modify
the used routine when two or more fields are cross
for join two table.

CLEAR
CREATE CURSOR test1 (v1 i)
CREATE CURSOR test2 (v1 i)
FOR ix=1 TO 3
	INSERT INTO test1 VALUES (m.ix)
	INSERT INTO test2 VALUES (m.ix)
ENDFOR

? "here vfp is changed"
* here VFP call it one time only
SELECT * ;
 FROM test1 t1 ;
  where t1.v1 > 0 OR udf()

? "here vfp is changed"
* here VFP call it one time only
SELECT * ;
 FROM test1 t1,test2 t2;
  where t1.v1 >0 OR t2.v1>0 OR udf()

?
? "here vfp is not changed"

* here VFP call udf useless
SELECT * ;
 FROM test1 t1 , test2 t2 ;
  where t1.v1 = t2.v1 OR udf()

* But ......

?
? "here vfp is changed"
* here VFP call it one time only and don't execute the comparison
SELECT * ;
 FROM test1 t1 , test2 t2 ;
  where t1.v1>0 OR t1.v1 = t2.v1 OR  udf()

?
? "here vfp is changed"
* here VFP call it one time only
SELECT * ;
 FROM test1 t1 , test2 t2 ;
  where t1.v1 = t2.v1 OR t1.v1>0 OR udf()
  
FUNCTION udf
* ? "Should never be called" 
* Fabio's comment: 
* this is incorrect because VFP check the function for a Boolean datatype,
* this is useless, but the SQL engine work in this manner

? "Should once be called" 
RETURN .f.

As it often happens, incoherent behaviors, but this is an old history.


Naturally for the matter of this thread nothing is changed.