Also you need to pad the trim(key1) out to a fixed number of characters or you will NOT het the correct output as VFP will make the length of the field equal to the length of the FIRST trim(key1) it finds.
i.e select distinct( Padr(trim(key1),20)) .... Dave -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Adam Buckland Sent: 04 January 2012 10:47 To: [email protected] Subject: Select statement... aaargh! 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.
