VFP 8 SQL "group by"

[LenaS]

According to a recent "FOXPRO ADVISOR" article you can no longer use the 'group by' statement using just the fields you want to group by. You must include every field in the table??

This would be quite cumbersome and insane if you have a table with 30 or so fields. Is it possible to use wildcards like 'group by custno, *'?

Also in preparing to convert to ver 8, I included the command 'set enginebehavior 70' in several programs, as the article suggested. This generates an error when trying to rebuild the project in Version 6. What do I do?

 

[Mike Gagnon]

Also in preparing to convert to ver 8, I included the command 'set enginebehavior 70' in several programs, as the article suggested. This generates an error when trying to rebuild the project in Version 6. What do I do?

Since your are using a command that only works in VFP8.0, recompiling in VFP6.0 would cause the error. I don't think you can have it both ways.

Mike Gagnon

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

 

[rgbean]

LenaS,
The "normal" way to get around version specific code is use conditionally compiled code or use a macro to delay the compile. e.g.

#if version(5) >= 800
   SET ENGINEBEHAVIOR 70
#endif

if version(5) >= 800
   xx = "SET ENGINEBEHAVIOR 70"
   &xx
endif

Both of these will work in VFP 6, 7 and/or 8.
Since version(5) didn't work in VFP 3/5, you'll need to use your own #DEFINE to use either of these techniques there.

Rick

 

[Mike Lewis]

Lena,

I know it sounds like a bad move on Microsoft's part, but they do have a good reason for changing the behaviour. Essentially, it's to make VFP more compatible with ANSI 92 SQL, which is the standard for SQL Server and many other back-ends.

As Rick suggested, you can overcome it by conditionally executing SET ENGINEBEHAVIOR 70. You might think it's a pity they didn't make that the default -- and I'd be tempted to agree with you -- but at least there is a solution.

Mike



Mike Lewis
Edinburgh, Scotland

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