>>Hi,
>>
>>I am building a SQL Command expression (to be used from VFP 9 application) where the JOIN is an expression.
>>
>>When I use the expression with IIF() for VFP data it works. Here is my VFP expression
>>
>>

>>cJoinExpr = "JOIN TABLE1 ON IIF(TABLE2.ORDER_NO>0,TABLE2.ID_FLD = 
>> TABLE1.ID_FLD, TABLE3.ID_FLD = TABLE1.ID_FLD) "
>>

>>
>>But when I change the expression to be used against SQL Server I get error. Here is my expression for SQL Server:
>>
>>

>>cJoinExpr = "JOIN TABLE1 ON CASE WHEN TABLE2.ORDER_NO>0 THEN TABLE2.ID_FLD =
>> TABLE1.ID_FLD ELSE TABLE3.ID_FLD = TABLE1.ID_FLD END "
>>

>>
>>What is wrong with my second (SQL Server) expression?
>
>I don't think you can make it work this way. Simply use two separate JOINS with a UNION, e.g. JOIN with the table based on the field and condition and then UNION with join based on another field with WHERE.
>
>Alternatively you can do
>
>JOIN TABLE1 ON (Table2.Order_NO > 0 and Table2.ID_Field = ..) OR (....)


You can :-)))))
Just check my answer :-))))))