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


> > 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.

Hi Boudewijn!

Thank you so much for the tip. It turned out that I encountered more and more of these errors as I went by. I tried to be a good coder by following the SQL new rules, but at this time there's just not enough time to retest and check every SQL clause I'm using in every program. This occurs mostly on SELECT-SQL that uses SUM() or COUNT() functions in the field list. The SET Enginebehavior 70, just what I needed..

Thank you so much,

Marty

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