>>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
Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
