>>>In addition to Michel's observation, use field names in GROUP BY clause, not numbers.
>>
>>Are you saying this as a matter of Best Practice, or because it absolutley does not work in Sql Server?
>>
>>I just want to understand the context of your suggestion.
>>
>>
>>On a related matter, I seem to recal a situation in VFP where you *have* to use numbers in either the Order or Group clause. Perhaps it was when doing a Union. I don't recall all the details.
>>
>>.
>
>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.


.