VFP8.0, Group BY - URGENT

[FoxLearner]

Hi all
I am facing a peculiar problem. I have a select statement like this:
SELECT sex, coverage, [06] AS agegrp, COUNT(age) AS agecount, agegroup, contract ;
FROM CensusTable ;
GROUP BY coverage, sex WHERE agegroup = "24"

This is working fine in VFP7.0 and 6.0. But in VFP8.0, I am getting an error:
"SQL: Group By Clause is missing or invalid".

Can some one come to my rescue?

Thanks and Regards
FoxLearner

 

[ramani]

Hi

SET ENGINEBEHAVIOR 70
will make a quick solution for all your such SQL problems. But follow the syntax and rectify soon to adapt to VFP8 will be better.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[rgbean]

Foxlearner,
You can also "fix" this by eliminating (or grouping on) the agegrp, agegroup and contract fields. As it stands these three fields are undefined in an SQL Select with a GROUP BY clause - they aren't being grouped and they aren't in a aggregate group function (SUM, COUNT, MIN, MAX, AVG, etc.).

Rick

 

[craigber]

It's fixed with a simple solution...it's called Read The Fine Manual (RTFM). IMO, SET ENGINE BEHAVIOR is not a good solution as the GROUP BY in previous versions could give ambiguous results. That's why the change was made in VFP8.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[FoxLearner]

Craig
Thanks for your reply and time. However, would you please explain it a little more? I couldn't follow.
Thanks
FoxLearner

 

[BPeisch]

Craig means you should read the online help, specifically, "What's New in Visual FoxPro 8.0" and the subtopic, "Behavior Changes Since Visual FoxPro 7.0". That topic explains how SQL Select syntax has changed. To quote from that topic,

The GROUP BY clause must list every field in the SELECT list, except for fields contained in an aggregate function, such as the COUNT( ) function. In addition, the GROUP BY clause must also list every field in a HAVING clause, except for fields contained in an aggregate function.





-BP

http://www.peisch.com