>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?
-----------------

Go for the UNION and your where clause will do the lifting you want. Otherwise you CANNOT use and index in your join(s) and you are in Table Scan mode. Big NO NO!

SQL operations are
Join
Where
Having
Field List
Order by