>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