VFP 8 Group By invalid 2

[robsuttonjr]

The following statement is invalid in Fox 8 not 7,6 etc., why and how to please.

SELECT * from custlist1 GROUP BY cust_no

 

[mpastore]

I *think* you need to explicitly name the field in the select clause. Check the release documentation.

 

[Mike Gagnon]

robsutonjr

The work around is to use:

SET ENGINEBEHAVIOR 70

Just before the SQL statement and re-issue:

SET ENGINEBEHAVIOR 80

Right after.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

As Mike suggests there is a work around, but the reason it doesn't work by default, is that the VFP team is trying to be more ANSI compliant. When you use a GROUP BY clause, then everything in the field list must either be in the GROUP BY list or be an Aggregate function like COUNT(), SUM(), MIN(), MAX(), etc. Any other field is truly undefined and FoxPro in the past simply gave you an arbitrary value - not even always the first or the last in the current "group". Any other ANSI compliant SQL environment will give you a similar error message.

Rick

 

[MichaelHawksworth]

Check out the whats new in VFP 8 section of help as you are likely to find a few of these, my particular pet hate is the select distinct variant.

The more common one of these is because of memo fields which you can at least cast into strings (although you have to pick a length to get). The hardest to get around are some of the union changes, although i think there may be a bug in the union code (still working through it)

Regards