>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?
.... ON TABLE1.ID_FLD = CASE WHEN TABLE2.ORDER_NO > 0
THEN TABLE2.ID_FLD
ELSE TABLE3.ID_FLD END
...
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.