GROUP BY with Memo field in VFP8

[StewartUK]

Using VFP8 without changing SET ENGINEBEHAVIOR, I've found that you can't run a SQL statement where one of the selected fields is a memo type and you use the GROUP BY keywords to get a cumulative field.

I had the statement

SELECT pkey,descr,explanation,cnt(donation.dondate) FROM acqwhycode JOIN donation ON whyfk=pkey WHERE groupfk=[u] GROUP BY 1,2 ORDER BY 4

where explanation is a memo field. This triggers the "GROUP BY clause is missing" error. Changing the statement to GROUP BY 1,2,3 triggers the same error.

Is there a way around this?

Thanks,

Stewart

 

[Mike Lewis]

Stewart,

I would have thought this was normal behaviour for VFP 8.0.

You'll agree that if Explanation was a char field, you'd expect to get the same message. As far as I know, the fact that it is a memo shouldn't make a difference.

As for how to get round it ... well, one solution would be SET ENGINEBEHAVIOR 70, but keep in mind that you will have no way of controlling which record within the group provides memo field.

An alternative would be to omit the memo from the query. Then do another query just to get the memo and the PK:

SELECT pkey, explanation FROM acqwhycode JOIN donation ON whyfk=pkey WHERE groupfk=[u] INTO CURSOR temp

and then join the two result sets together on the PK.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[StewartUK]

Thanks for the reply Mike - running the second query is an easy work around.

I may have misread what you wrote, but I think you may have missed that I mentioned that I get the error if I include the memo field in the GROUP BY list. I couldn't see anywhere that the help says that you can't use a memo field to GROUP BY.

Stewart

 

[Mike Lewis]

Stewart,

I think you may have missed that I mentioned that I get the error if I include the memo field in the GROUP BY list.

Yuu're right. I didn't take that in properly. Although I don't think that invalidated my reply.

I can't see any reference to this in the Help, but it wouldn't surprise me if you can't group by (or order by) a memo field. After all, VFP has to do a lot of work to compare and sort on memos, and it might well decide that it can't be bothered.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MadTown]

Here's the reference in the Help to the Memo field in a group by clause issue. It's under the What's New in VFP8 Section under behavior changes since VFP7.0



Visual FoxPro contains a new SET ENGINEBEHAVIOR command so that you can control SQL data engine compatibility. This impacts how SQL SELECT commands function. The current default behavior can be set to previous behavior using the SET ENGINEBEHAVIOR command and is described as follows:

You cannot use the DISTINCT clause with Memo or General fields in a SQL SELECT statement. Instead, wrap a Memo field expression inside a function such as PADR( ) or ALLTRIM( ).
The GROUP BY clause does not support Memo or General fields.
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.

Ken