Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Boudewijn Lutgerink
  Where is Boudewijn Lutgerink?
 Hoonaardstraat, Driel
 Netherlands
 Boudewijn Lutgerink
 To: Marty Merino
  Where is Marty Merino?
 Bellflower
 California - United States
 Marty Merino
 Tags
Subject: RE: Weird SQL error in Version 8.0 not on Versi...
Thread ID: 34002 Message ID: 34069 # Views: 0 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, December 08, 2003 8:14:24 AM         
   


> Hi, All!
>
> I'm here again, asking for your help for this particular error I'm getting as I went ahead and tried to convert version 6.0 programs to version 8.0. I have this SQL command that has been working and used more frequently than any other program in our system currently compiled in version 6.0.
>
> SELECT CLIENTS.cGROUP, CLTLOG.cCode, CLTLOG.cSource, INFOLOG.cExName, GENCOMP.cName, CLIENTS.cType, CLIENTS.nBillPage, ;
> SUM(CLTLOG.nSingle) AS nSingle, SUM(CLTLOG.nAccMul) AS nAccMul, SUM(CLTLOG.nUndeliv) AS nUndeliv ;
> FROM CLTLOG, CLIENTS, GENCOMP, INFOLOG ;
> INTO CURSOR cuClient ;
> GROUP BY CLTLOG.cSource,CLTLOG.cID ;
> WHERE CLIENTS.cCode = LEFT(INFOLOG.cSource,3) ;
> AND CLIENTS.cGroup = lcGroup ;
> AND GENCOMP.cID = CLTLOG.cID ;
> AND GENCOMP.cCode = INFOLOG.cCode ;
> AND CLTLOG.cSource = INFOLOG.cSource ;
> AND CLTLOG.cSubCode = lcSubCode ;
> AND INFOLOG.cSubCode = lcSubCode ;
> AND INFOLOG.tTimeStamp >= ldDate1 ;
> AND INFOLOG.tTimeStamp <= ldDate2 ;
> AND NOT EMPTY(INFOLOG.tProcess) ;
> AND INFOLOG.cSrcKey NOT IN (SELECT cSource FROM INFOSKP WHERE cBillSkp = 'Y') ;
> ORDER BY CLTLOG.cSource,CLTLOG.cID
>
> What could have caused this command to Error No. 1807 - SQL: GROUP BY clause is missing or invalid.?
>
> I'd really appreciate your help.
>
> Marty

As Eric already told, this error can be prevented by using "set enginebehavior 70".
The reason for this error is that the VFP8 SQL engine applies more strictly to the ANSI SQL rules than 7 did.
In order to prevent this error you should include every field in the "GROUP BY" clause (of course in the most prevalent order).
Maybe the second error you might run in to is an error on the "ORDER BY". Same applies here then.

still, set enginebehavior 70 is a whole lot less typing..


Boudewijn LutgeĀ®ink
Boudewijn.Lutgerink@foxite.com
It's better to drive safely in five o'clock traffic than be featured on the six o'clock news.


ENTIRE THREAD

Weird SQL error in Version 8.0 not on Version 6.0 Posted by Marty @ 12/6/2003 1:48:10 AM
RE: Weird SQL error in Version 8.0 not on Versi... Posted by Eric den Doop @ 12/6/2003 2:02:14 AM
RE: Weird SQL error in Version 8.0 not on Versi... Posted by Marty @ 12/6/2003 2:17:49 AM
? Shirts where and how ? Posted by suhas hegde @ 12/6/2003 6:36:44 AM
RE: ? Shirts where and how ? Posted by Eric den Doop @ 12/6/2003 10:46:41 AM
RE: Weird SQL error in Version 8.0 not on Versi... Posted by Marty @ 12/6/2003 2:10:56 AM
RE: Weird SQL error in Version 8.0 not on Versi... Posted by Boudewijn Lutgerink @ 12/8/2003 8:14:24 AM
RE: Weird SQL error in Version 8.0 not on Versi... Posted by Marty @ 12/10/2003 10:33:21 PM