>>I think best practice is to use field names in SQL Server.
>>
>>See also this very interesting story
>>http://bradsruminations.blogspot.com/2010/01/trolls-puzzle-sql-fable.html
>>
>>In the UNION clause in VFP you have to use ordinals.
>
>Well, you were right...
>
> The VFP version of the Select statement ran fine with "Group by 3,4", but when I tried to use that on a Sql Server call, it told me this error message:
>

>Each GROUP BY expression must contain at least one column that is not an outer reference. [1526:164]
>

>
>So, I change it to use Table.Field references like this "'Group by MergedTime.mach_num, Machines.mach_name', and then it works in Sql Server, but it broke it in VFP (since VFP only work with ordinals when working with a Union)
>
>So, I made a little conditional that looks at the data access mode of my busniess class (0= DBF, 2 = Sql Server).
>
>

> If this.nDatamode = 0
>  lcGroupByClause = 'Group by 3,4' 
> Else
>  lcGroupByClause = 'Group by MergedTime.mach_num, Machines.mach_name' 
>EndIf
>

>
>
>It's a little extra work, but not too bad to add this to a few Selects. This will allow me to get through the migration process as I test my app running against Sql Server.
>
>
>.
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.