GROUP BY clause is missing or invalid 1

[Niki_S]

When I run my code it says "GROUP BY clause is missing or invalid". What is the issue in this code?

SELECT nStylecode,cAccountNo,cContract,cStyleNumber as Style,cLotName,cCategory,nCiQty,cShipFty,cColorName,cShipMode,dEx_dte,dindc_dte,;
cDestination as Destinatn,cPoNo,cSeason,000000000 as Delvr_Pcs,000000000 as nqty FROM _DelClr GROUP BY cColorName INTO Dbf C:\Tempfiles\_DelDtl

Thank you

 

[Mike Lewis]

The rule states that, whenever you have a query that involves grouping, each field in the result set (that is, each expression immediately following the word SELECT) must be either: (i) one of the fields involved in the grouping; or (ii) an aggregate expression, such as SUM(), AVG(), COUNT(), etc.

Your SELECT statement does not follow that rule, hence the error.

By the way, this is not a Foxpro rule. It applies to SQL generally.

There is a good reason for this rule. Your result set will contain one record for each cColorName. That's the whole point of grouping. So how would the query know which nStylecode, cAccountNo, etc. to put into that record?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rick C. Hodgin]

You can get around the requirement that Mike stated by doing this:

SET ENGINEBEHAVIOR 70
* Do your SQL SELECT here
SET ENGINEBEHAVIOR 90

The older engine allowed the malformed GROUP BY clause, which often has valid utility but is technically data-incorrect.

--
Rick C. Hodgin

 

[Mike Lewis]

What Rick says is correct. But keep in mind that, if the various fields following the word SELECT (nStylecode, cAccountNo, etc.) are not all unique within each group, the results will be somewhat meaningless. That's why the rule exists. But give it a try, by all means.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I wrote about this issue here:

http://www.tomorrowssolutionsllc.com/Articles/Using SQL the VFP 8 way.PDF.

I do not think you should ever use SET SQLBEHAVIOR 70, except with older code that you haven't yet had time to fix. No new code should rely on that behavior.

Tamar

 

[Niki_S]

Thank you all for the helps. I fixed it .