macro substitution error in select statment

[KALEMO]

This statement works in VFP 2.6, but i get a "Group By Clause is missing or invalid" error when it is run in VFP 9:

sele lctable as table, lccond as condition, lcsource as grsource, ;
iif(type(lcsource) = "C", padr(alltrim(&lcsource),14), ltrim(trans(&lcsource, "$$$,$$$,$$$.99"))) as total, ;
coun(&lcsource) as cnt, recc() as totl, glc1, glc2, glc3, glc4, glc5, glc6, glnt ;
from datafile where glnt = grcode group by &lcsource into table temp

 

[bborissov]

Starting with VFP8 there is a lot of improvements in SELECT-SQL command. You could check what they are if you read:
SET ENGINEBEHAVIOR topic in HELP
and SQL Language Improvements under What's New in Visual FoxPro section also in HELP.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Kalemo,

The rule for the GROUP BY clause is simple:

[blue]If you have a GROUP BY in your SELECT, then each column in the result set must either be: one of the fields that you are grouping on; or a summary function, such as SUM() or MAX().[/blue]

You have lcCond in your result set. This does not conform to the above rule.

The rule was introduced in VFP 8.0 (to bring VFP more into line with ANSI-92 SQL). You can override it by setting ENGINEBEHAVIOR to 70, but it is better to recast your SELECT statements to conform to the rule.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk