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:profox-boun...@leafe.com] 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 <fox...@ozemail.com.au> 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:profoxtech-boun...@leafe.com] On Behalf Of jerry
foote
Sent: Saturday, 4 January 2014 12:50 PM
To: profoxt...@leafe.com
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: ProFox@leafe.com
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/52c81430.5020...@fibertel.com.ar
** 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.
