Hi sergey,

Yep,

CREATE CURSOR orders (ord_pk I, ord_dummy C(10))
CREATE CURSOR Orderlines (orl_pk I, orl_ordfk I, orl_itmfk I)
CREATE CURSOR Items (itm_pk I, itm_name C(10))

SELECT * FROM orders O ;
	WHERE EXISTS(SELECT 1 FROM orderlines INNER JOIN Items ON orl_itmfk = itm_pk ;
		WHERE orl_ordfk = O.ord_pk AND itm_name = "BIKE") OR ;
	EXISTS(SELECT 1 FROM orderlines INNER JOIN Items ON orl_itmfk = itm_pk ;
		WHERE orl_ordfk = O.ord_pk AND itm_name = "SCOOTER")

Runs without a problem in VFP8 and errors in VFP9. Note that if you change the OR into AND, it does not error.

From further investigations it seems that it also does not error if the SELECT commands within a EXISTS() only referecence one table, it does not error also.


So it errors when:

1. An OR is used between two exists
2. At least one of the EXISTS(SELECT ...) references at least two tables.

Walter,


>Walter,
>
>Can you provide complete repro code?
>
>>VFP9 the error when executing the command below:
>>
>>

SELECT trm_acu_id as Acu_id, trm_cycleno as Cycleno,  trm_partner as Partner, Trm_ovumdonor as Ovumdonor, ;
>>	trm_semdonor as semdonor,  trm_ovumcycle as Ovumcycle ;
>>	FROM t_Treatmen Tr  ;
>>		WHERE ( EXISTS (SELECT Prn_acu_id AS Acu_id FROM t_prognotes T,  t_progdets  ;
>>			WHERE Prn_acu_id = Tr.Trm_acu_id AND prn_enc_cycle = tr.trm_cycleno AND Prd_Numvalue >  200 AND ;
>>				prd_mdsfk =      70105 AND  prn_enc_id = prd_enc_id) OR ;
>>		EXISTS(SELECT Prn_acu_id AS Acu_id FROM t_prognotes T,  t_progdets ;
>>			WHERE prn_acu_id = tr.trm_acu_id AND prn_enc_id = prd_enc_id AND  prn_enc_cycle=SPACE(10) ;
>>				AND Prd_Numvalue >  200 AND prd_mdsfk =      70105  ))

>>
>>This command runs without any problem in VFP8. The error occurs regardless of SET ENGINEBEHAVIOR.
>>Interestingly when I change the OR operator between the two EXISTS into AND, it runs without a problem
>>
>>A real showstopper in my app as these kind of SQL occur frequently.