This query runs fine in VFP Sql, but it does not work when I try to use it against Sql Server.

All I know is that I'm getting this error:

Incorrect syntax near '='. [1526:102]

I'm guessing the problem(s) lie in the Cast() and IIF() functions, and maybe other areas.

I'm sure I'm about to get my tail spanked with this project... I'm preparing for conversion of my app from dbf table to SqlServer back end. The basic Selects are easy to convert, but I have a few like this, which I can tell are going to cause me some pain.

Anybody know where the problems are?

The tables were upsized into Sql Server from VFP DBC using the ltest Sedna Upsizing Wizard. VFP Date fields were converted to DateTime, but I assume you can still use Max() and Min() on DateTime fields, so let's hope that will work fine.

Select Min(Workday) as MinDate,
 Max(Workday) as MaxDate,
 MergedTime.mach_num as Mach_Num,
 Machines.mach_name as Mach_Name,
 Cast(sum(hrs_worked) as Numeric(10,2)) as Hrs_Worked,
 Max(ActiJobs.Mach_rate) as Mach_Rate,
 Cast(sum(hrs_worked*actijobs.mach_rate) as Numeric(10,2)) as Total
 From ActiJobs MergedTime
 Join prempl02 employees on MergedTime.emp_num = employees.prempl
 Join machines on MergedTime.mach_num = machines.mach_num
 Where job_num='4033X'
 Group by 3,4
   Union 
Select Min(Date),
 Max(Date),
 TimeClock.mach_num,
 Machines.mach_name,
 Cast(sum(Iif(TimeClock.hrs=0, 23.5667 - TimeClock.start, TimeClock.hrs)) as Numeric(10,2)),
 Max(machines.Mach_rate),
 Cast(sum(Iif(TimeClock.hrs=0, 23.5667 - TimeClock.start, TimeClock.hrs) * machines.mach_rate) as Numeric(10,2))
From Clock TimeClock
 Join prempl02 employees on TimeClock.emp_num=employees.prempl
 Join machines on TimeClock.mach_num=machines.mach_num 
 Where job_num='4033X' and polled=.f.
 Group by 3,4
 Order by 1,3

Here's the actual method that created this Sql :

LPARAMETERS tuParentKeyOrRef, tcMatchField, tcFieldList, tcOrderByFieldList, tcCursor, tlReadWrite, tlCloseCursor
*-- Note, the passed cFieldList will not be used in this case, because this method has hard-coded fields to return.

Local loReturn, lcGroupByClause, lcSql, lcTimeClockAlias, lnCurrentTime

If Empty(tcOrderByFieldList)
 tcOrderByFieldList = '1,3'
Endif

lcTimeClockAlias='Clock'
lcGroupByClause='Group by 3,4' 
lnCurrentTime = CurrentTimeInDecimal() && Procedure required from my lmUtil proc file

this.PrepareSqlCommandParts(tuParentKeyOrRef, tcMatchField, tcFieldList, tcOrderByFieldList, tcCursor, tlReadWrite, tlCloseCursor)

*-- Build SQL Command. Note this SQL is good for both DBF mode and Slq Server mode
*-- First, we'll pull merged time records, then we'll union in records form the Time Clock,
*-- which have not been merged into the main time records table yet.
Text to lcSql PRETEXT 15 TEXTMERGE NOSHOW
    Select Min(Workday) as MinDate,
           Max(Workday) as MaxDate,
           MergedTime.mach_num as Mach_Num,
           Machines.mach_name as Mach_Name,
           Cast(sum(hrs_worked) as Numeric(10,2)) as Hrs_Worked,
           Max(ActiJobs.Mach_rate) as Mach_Rate,
           Cast(sum(hrs_worked*actijobs.mach_rate) as Numeric(10,2)) as Total
    From <<this.cFromAlias>> MergedTime
     Join prempl02 employees on MergedTime.emp_num = employees.prempl
     Join machines on MergedTime.mach_num = machines.mach_num
    Where <<this.cWhereClause>>
    <<lcGroupByClause>>
    <<this.cIntoCursor>> <<this.cReadWrite>>
   Union
     Select Min(Date),
            Max(Date),
            TimeClock.mach_num,
            Machines.mach_name,
            Cast(sum(Iif(TimeClock.hrs=0, <<lnCurrentTime>> - TimeClock.start, TimeClock.hrs)) as Numeric(10,2)),
            Max(machines.Mach_rate),
            Cast(sum(Iif(TimeClock.hrs=0, <<lnCurrentTime>> - TimeClock.start, TimeClock.hrs) * machines.mach_rate) as Numeric(10,2))
     From <<lcTimeClockAlias>> TimeClock
      Join prempl02 employees on TimeClock.emp_num=employees.prempl
      Join machines on TimeClock.mach_num=machines.mach_num
     Where <<this.cWhereClause>> and polled=.f.
     <<lcGroupByClause>>
     <<this.cOrderBy>>
EndText

loReturn = this.LoadLineItems(.f., .f., .f., .f., .f., .f., .f., lcSql)

Return loReturn