In addition to Michel's observation, use field names in GROUP BY clause, not numbers.

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