Hi nadya,

>I could not make it work. And I found that OR prevents using indexes. That's a little bit strange. Why AND allows to use indexes and OR doesn't?

With OR, both sides of the expression need to be rushmore optimizable in order for the whole expression to be optimizable. However in complex situation even where all arguments are optimizable and from a logical point of view VFP could optimize the expression, VFP often finds the expression too complex and reverts to a table scan.

One example I got recently was:

SELECT ..... ;
	FROM  t_relations  ;
		LEFT JOIN t_Treatmen ON rel_cycleno = trm_cycleno AND rel_cycleno <> "      " ), t_demo ;
	WHERE (rel_acu_id = ?Demo.acu_id AND  dem_acu_id = rel_related_to) OR  ;
		(T_relations.rel_related_to = ?Demo.acu_id AND  dem_acu_id = rel_acu_id )

All field used in the WHERE clause were optimizable, but it did not optimize the expression. I found myself rewriting the statement into

SELECT ....
	FROM  t_relations   
		INNER JOIN t_Demo ON (dem_acu_id = rel_related_to AND rel_acu_id = ?demo.Acu_id)  
		LEFT OUTER JOIN t_Treatmen  ON  Rel_cycleno = trm_cycleno AND rel_cycleno > SPACE(10)  ;
UNION SELECT .... ;
	FROM  t_relations   ;
		INNER JOIN t_Demo ON dem_acu_id = rel_acu_id AND rel_related_to = ?demo.Acu_id ;
		LEFT OUTER JOIN t_Treatmen  ON  Rel_cycleno = trm_cycleno AND rel_cycleno > SPACE(10)

The latter was both in VFP and SQL server way and way quicker than the first mainly because of optimizing reasons. BTW, the example above are also (updatable) views.

Walter,