SELECT SQL problem with Fox V8.0 2

[baudouxf]

Hello,

This very simple Query works fine with FoxPro V7:

SELECT Calls.key, Calls.arch_key, Calls.datetime;
FROM main!calls;
GROUP BY Calls.datetime;
ORDER BY Calls.datetime

With V8 (SP1 installed or not) it gives :
'SQL:GROUP BY clause is missing or invalid'

I tried several simple Queries and each time this message appears when a GROUP clause is used.

If I remove the GROUP clause, it works.

Thanks for your help

 

[TheSofty]

The short answer is that you have to include all the fields in the GROUP BY clause. For the longer one see SET ENGINEBEHAVIOR in the VFP 8.0 help. It's a new feature.

TheSofty

 

[TheRambler]

This article is worth reading:

INFO: SQL SELECT Behavior Changes in Visual FoxPro 8.0

http://support.microsoft.com/default.aspx?scid=kb;en-us;813361&Product=Vfp8

 

[treetops]

So, is the intent of this to force you not to display fields for which the data is arbitrary (ie, the last entry in the list of records sharing a value in the "grouped by" field)? Is it logically impossible to have a field you want to display (SQL:select) but that you don't want to group by (aside from aggregate fields)?

 

[rgbean]

Treetops,
You can be "creative" to get those fields that you want to show a value for but aren't being grouped on. e.g. the MAX(fieldx) or MIN(fieldx) function works to give you just an arbitrary value as the pre-VFP 8.0 does.

Rick

 

[craigber]

This is one of those things that is explained in the help file. You should ALWAYS look at What's New when each version ships.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

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

 

[StewartUK]

Craig is absolutely right.

As I see it, Microsoft's intent with this change in GROUP BY is to bring VFP's version of SQL in line with standard SQL syntax.

Rick - could you expand a little on your post about using the MAX & MIN functions?

Thanks,

Stewart

Hope that helps,

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

 

[craigber]

The old way (pre-v.80) could cause ambiguous results.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

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

 

[rgbean]

Stewart,
Since you just want a value for the columns you aren't grouping by (or aren't COUNT()ing, SUM()ing or AVG()ing), you can get "a" value by using MIN() or MAX(). Since these two functions work on all data types, they are a good substitute for just using the field name (like in pre-8.0). That is before you didn't care what you got in these columns, so why care now! e.g.

CREATE CURSOR temp (fld_c1 c(10),fld_c2 c(10), fld_i int, ;
   fld_n n(5,2), fld_l l, fld_d d, fld_t t)
INSERT into temp VALUES ("XYZ", "ABC", 1, 3.11, .T., DATE(), DATETIME())
INSERT into temp VALUES ("ABC", "XYZ", 2, 7.34, .T., {^2004/01/02}, {^2004/05/08 12:41:00})
INSERT into temp VALUES ("XYZ", "DEF", 3, 0.00, .F., {^2003/07/04}, {^2006/07/18 00:02:05})
INSERT into temp VALUES ("XYZ", "DEF", 3, 9.99, .F., {^2003/07/04}, {^2006/07/18 00:02:05})

SELECT fld_c1, ;
 MIN(fld_c2) as fld_c2, MAX(fld_i) as fld_i, ;
 MAX(fld_n) as fld_n, MIN(fld_l) as fld_l, MIN(fld_d) as fld_d, ;
 MAX(fld_t) as fld_t ;
  FROM temp ;
  GROUP BY fld_c1 ;
  INTO CURSOR curTemp
BROWSE LAST