VFP9 and SQL Group By error 1

[TonyScarpelli]

With this sql code I am getting an error:

SELECT accntnum, datefrom, dateto, type, hours, SUM(hours);
FROM Overtime ;
WHERE datefrom BETWEEN gdstart AND gdend ;
GROUP BY type ;
ORDER BY type ;
INTO CURSOR TYPETOTL

SQL: GROUP BY clause is missing or invalid

Can any tell me why this code, which worked in FPW2.6 doesn't work in VFP 9?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[crewchiefpro6]

The Group By clause has changed. See the help section on SQL about the changes.

OR

SET ENGINEBEHAVIOR TO 70

This will allow you to keep your existing code.

Don


Don Higgins

http://www.crewchiefpro.com

 

[Mike Lewis]

Tony,

As Don says, you can issue SET ENGINEBEHAVIOR TO 70, and your code will work again. However, that's not the whole story.

In ASNI-92 SQL, the rule is: If a SELECT contains a GROUP BY clause, then the columns in the result set (that is, the fields listed immediately after the keyword SELECT) must either be aggregate functions (such as SUM()) or must themselves be listed in the GROUP BY.

The reason is simple. Given the result set will have one record per Type (in your case), how would it know which values to use for Accntnum, Datefrom and Dateto? Those fields occur in every record, and could contain different values in each record in a given group.

VFP was always relaxed about enforcing this rule, but with VFP 9.0, it is moving closer to the ANSI-92 standard, and is much stricter about compliance. To avoid breaking existing code, you can put the clock back by issuing SET ENGINEBEHAVIOR TO 70, but it would be better to take a closer look at the SELECT, ask yourself if it is really doing what you want, and re-code it as necessary.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jesylv]

Hi Mike,

Thanks for this explanation. Very clear indeed.

Jean

http://www.sigp.ca

 

[TonyScarpelli]

Thanks all. Old brain not up to it's younger days.

After reading the changes in the help file, and the SQL On Line help about 20 times I think I got it in my head.

After changing the code to

SELECT Overtime.accntnum, Overtime.type, Overtime.datefrom,;
SUM(Overtime.hours);
FROM Overtime;
WHERE Overtime.datefrom BETWEEN gdstart AND gdend;
GROUP BY Overtime.accntnum, Overtime.type, Overtime.datefrom;
ORDER BY Overtime.accntnum;
INTO CURSOR typetotal

it all worked fine. Had to figure out (in an easy to understand interpretation <g>) that you just have to put all the SELECT fields in the GROUP BY except the function; and that's about what you said. I also removed the fields I didn't need.

Thanks again. CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[JimWWinter]

Tony,

It's not a big deal, but you can also use the numeric positon of the fields in the SELECT clause to specify the GROUP BY and ORDER BY clauses, e.g.

SELECT Overtime.accntnum, Overtime.type, Overtime.datefrom,;
SUM(Overtime.hours);
FROM Overtime;
WHERE Overtime.datefrom BETWEEN gdstart AND gdend;
GROUP BY 1, 2, 3 ;
ORDER BY 1 ;
INTO CURSOR typetotal

I find it helps me avoid typos.

Regards,
Jim

 

[alvechurchdata]

I find it helps me avoid typos

I sympathise with the problem but using these numbers does make maintenance more difficult - especially on the more complex statements where you see something like ORDER BY 23, 42, 17.

I avoid the typos by using the Query Designer and dragging the code from SQL view into my program.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

A more compelling reason not to use column numbers in a GROUP BY is that it is not ANSI standard SQL. If you want to write code that is portable is, say, SQL Server, you would have to use column names.

Interestingly, SQL Server does allow column numbers in ORDER BY.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[JimWWinter]

Thanks, Mike and Geoff. Most of my queries are not that complex and run against VFP tables. But I didn't know that it wasn't ANSI compliant. Guess I'll have to change my ways!

Regards,
Jim