Thanks Peter, SET BEHAVIOUR 70 worked as did using all fields except the sum() etc...
Cheers... onwards and upwards.. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Peter Cushing Sent: 04 January 2012 10:58 To: [email protected] Subject: Re: Select statement... aaargh! Hi Adam, Try group by 1 and see if that works. It may also work with group by distinct(trim(key1)) I don't have VFP9 so can't test it. When doing a group by you are supposed to group by all fields except for the columns with functions like SUM() COUNT() ETC. Older versions of VFP SQL will allow you do get away without doing that. I think there is a command SET ENGINEBEHAVIOR to 6 or something similar to set it back to an earlier version. HTH * Peter Cushing * IT development and support Signature Adam Buckland wrote: > I run a routine once a year and have just upgraded to VF9.... > > The following statement is falling over with "SQL group by clause is > missing or invalid": > > select distinct(trim(key1)), cust, pcode, sum(sale) as sold from > tempsac group by key1 into table usage > > The structure of the table is : > > > Structure for table: > C:\USERS\BUCA\DOCUMENTS\WORK\REGULAR JOBS\PRODUCT WEIGHTS > VALPAK\2012\TEMPSAC.DBF > Number of data records: 6698 > Date of last update: 01/04/12 > Code Page: 1252 > Field Field Name > Type > Width Dec Index Collate > Nulls Next > Step > 1 CUST > Character > 10 > No > 2 KEY1 > Character > 32 > No > 3 PCODE > Character > 22 > No > 4 SALE > Numeric > 16 > No > ** Total ** > > It ran ok under VFP6 but have no copy of VFP6 now... I've banged my > head against the wall for over an hour and that hasn't helped so maybe > someone can spot the mistake! > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
