There is also ICASE function - I also think perhaps FixSQL can not handle all complex cases.

>>You have IIF here - are you passing this SQL through some utility again?
>
>Yes,I have a method called FixSql() that will modify the passed in VFP Select to work on Sql Server, addressing things like Iif() and such.
>
>I add to it every time I find a case that needs to be addressed.
>
>Here is that code:
>
>

>Lparameters tcSql
>
>*-- 2010-02-28: Added this method to fix up my Sql statements...
>*-- My Sql statements are written for VFP Selects, so when the run against Sql Server, they need a little work
>*-- to adjust them to the correct syntax for Sql Server.
>
>*-- See other ways to handle this here: http://www.west-wind.com/wwThreads/default.asp?Thread=2PP17MHZ0&MsgId=2PR1FFRHX
>
>If this.nDataMode = 2
> tcSql = Strtran(tcSql, 'nvl(', 'IsNull(', 1, 999, 1)
> tcSql = Strtran(tcSql, '==', '=', 1, 999, 1)
> tcSql = Strtran(tcSql, 'substr(', 'SubString(', 1, 999, 1)
>
> tcSql = Strtran(tcSql, '.t.', '1', 1, 999, 1)
> tcSql = Strtran(tcSql, '.f.', '0', 1, 999, 1)
>
> tcSql = this.FixSqlField(tcSql, 'order') && wraps in brackets like [order], [desc], [view], etc.
> tcSql = this.FixSqlField(tcSql, 'desc')
> tcSql = this.FixSqlField(tcSql, 'view')
>
> tcSql = Strtran(tcSql, ' ReadWrite', '', 1, 999, 1) && Strip this out. Sql Server queries are automatically ReadWrite.
>
> *-- Change VFP Iif() calls to T-Sql Case / End structure
> *-- Uses Craig Boyd's RegExp fll. It must be registered in the the app boot strapper.
> lcPattern =  '[iI][iI][fF]\(([^,]*),([^,]*),([^\)]*)\)'
> tcSql = RegExp(tcSql, lcPattern, 1, ' case when \1 then \2 else \3 end ')
> 
>
>EndIf
>
>Return tcSql
>
>