Thank you Rafael that was what I what I was looking for. Jerry
<-----Original Message----- <From: ProFox [mailto:[email protected]] On Behalf Of Rafael Copquin <Sent: Saturday, January 04, 2014 8:01 AM <To: [email protected] <Subject: Re: Invalid Group By <I would add that you need to put in the group by clause all the <non-calculated fields in the select part of your statement, only if SET <ENGINEBEHAVIOR IS 80 or 90, which is in line with what you would use if <your select were directed at pulling records from an external engine, <such a s MS SQLSERVER or MySQL. <If you are only dealing with DBF's, you can use your original group by <clause if you SET ENGINEBEHAVIOR 70 <Rafael Copquin <El 04/01/2014 0:37, jerry foote escribió: > Thank Guys > Jerry > > -----Original Message----- > From: ProFox [mailto:[email protected]] On Behalf Of Stephen Russell > Sent: Friday, January 03, 2014 9:25 PM > To: ProFox Email List > Subject: Re: Invalid Group By > > Group by MANI_ID,MANIFEST_N,BILL_OF_LA,transfers,transfr_no,to_acct is what > you need. > > > On Fri, Jan 3, 2014 at 9:05 PM, Darren <[email protected]> wrote: > >> Try >> >> SELECT ; >> MANI_ID, ; >> MANIFEST_N, ; >> BILL_OF_LA, ; >> transfers, ; >> transfr_no, ; >> to_acct, ; >> SUM(TOTAL_UNIT) AS TOTAL_UNIT, ; >> SUM(RELEASED) AS RELEASED, ; >> CAST(0 AS I) AS releacnt, ; >> CAST(0 AS I) AS SUMTRANS, ; >> CAST(0 AS I) AS SUB_BALANCE, ; >> CAST(0 AS I) AS BALACEIS ; >> FROM transfers ; >> where to_acct = LOOK_ACCT AND TRANSFERS > 0 AND FROMSOURCE = "TRANSFER" ; >> GROUP BY ; >> MANI_ID, ; >> MANIFEST_N, ; >> BILL_OF_LA, ; >> transfers, ; >> transfr_no, ; >> to_acct ; >> INTO CURSOR selected1 READWRITE >> >> -----Original Message----- >> From: ProfoxTech [mailto:[email protected]] On Behalf Of jerry >> foote >> Sent: Saturday, 4 January 2014 12:50 PM >> To: [email protected] >> Subject: Invalid Group By >> >> "LOOK_ACCT=127 >> >> select >> MANI_ID,MANIFEST_N,BILL_OF_LA,transfers,transfr_no,to_acct,SUM(TOTAL_UNIT) >> AS TOTAL_UNIT, ; >> >> SUM(RELEASED) AS RELEASED,0000000 as releacnt ,00000000 AS SUMTRANS, > 000000 >> AS SUB_BALANCE ,000000 AS BALACEIS; >> >> FROM transfers where to_acct=LOOK_ACCT .AND. TRANSFERS>0 AND >> FROMSOURCE="TRANSFER"; >> >> INTO CURSOR selected1 READWRITE GROUP BY mani_id" >> >> >> >> When I execute the above code I get error "SQL: GROUP BY clause is missing >> or invalid.",even when I leave the group by off I get same error. >> >> >> >> Any help as to what is wrong would be appreciated. >> >> >> >> Thanks Jerry >> >> >> >> --- StripMime Report -- processed MIME parts --- multipart/alternative >> text/plain (text body -- kept) >> text/html >> --- >> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/C66E41A3962E4CD8ABEEE12E832FCBF4@jerryfootePC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
