You have IIF here - are you passing this SQL through some utility again?

>>If you have GROUP BY in each part of the UNION, then using field names should work just fine in VFP. It's the order by which is problematic.
>
>
>
>You are correct! Once you said that, I did some more testing and found out what I was doing wrong. As long as my table aliases involved in the Group By both match between each part of the Union, I can use the same Group By clause for each part of the Union, and it can be Alias.Field syntax rather than ordinal, so the final Select *does* work in both VFP and Sql Server.
>
>
>Here's my method code:
>

>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 AS Object
>Local lcGroupByClause, lcSql, lcTimeClockAlias, lnCurrentTime
>
>If Empty(tcOrderByFieldList)
> tcOrderByFieldList = '1,3'
>Endif
>
>lcTimeClockAlias = 'Clock'
>
>lcGroupByClause = 'Group by TimeRec.mach_num, Machines.mach_name' 
>
>lnCurrentTime = CurrentTimeInDecimal() && Procedure required from my lmUtil proc file
>
>this.PrepareSqlCommandParts(tuParentKeyOrRef, tcMatchField, tcFieldList, tcOrderByFieldList, tcCursor, tlReadWrite, tlCloseCursor)
>
>*-- Build SQL Command... Note this VFP Select syntax, and will require a pass through th FixSql() method on 
>*-- the wwBusinessPro class to fix the SQL when used on Sql 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,
>           TimeRec.mach_num as mach_num,
>           Machines.mach_name as mach_name,
>           Cast(sum(hrs_worked) as Numeric(10,2)) as Hrs_Worked,
>           Max(TimeRec.Mach_rate) as Mach_Rate,
>           Cast(sum(TimeRec.hrs_worked * TimeRec.mach_rate) as Numeric(10,2)) as Total
>    From <<this.cFromAlias>> TimeRec
>     Join Prempl02 Employees on TimeRec.emp_num = Employees.prempl
>     Join Machines on TimeRec.mach_num = Machines.mach_num
>    Where <<this.cWhereClause>>
>    <<lcGroupByClause>>
>    <<this.cIntoCursor>> <<this.cReadWrite>>
>   Union
>     Select Min(Date),
>            Max(Date),
>            TimeRec.mach_num,
>            Machines.mach_name,
>            Cast(sum(Iif(TimeRec.hrs=0, <<lnCurrentTime>> - TimeRec.start, TimeRec.hrs)) as Numeric(10,2)),
>            Max(Machines.Mach_rate),
>            Cast(sum(Iif(TimeRec.hrs=0, <<lnCurrentTime>> - TimeRec.start, TimeRec.hrs) * machines.mach_rate) as Numeric(10,2))
>     From <<lcTimeClockAlias>> TimeRec
>      Join Prempl02 Employees on TimeRec.emp_num = Employees.prempl
>      Join Machines on TimeRec.mach_num = Machines.mach_num
>     Where <<this.cWhereClause>> and polled=.f.
>    <<lcGroupByClause>>
>     <<this.cOrderBy>>
>EndText
>
>loReturn = this.LoadLineItemsBase(lcSql)
>
>Return loReturn
>
>