Index
2014-01-03 19:49 – jerry foote : Invalid Group By
2014-01-03 21:05 – Darren : RE: Invalid Group By
2014-01-03 21:24 – Stephen Russell : Re: Invalid Group By
2014-01-03 21:37 – jerry foote : RE: Invalid Group By
2014-01-04 08:01 – Rafael Copquin : Re: Invalid Group By
2014-01-04 08:22 – jerry foote : RE: Invalid Group By
2014-01-09 08:46 – Stephen Russell : Re: Invalid Group By
2014-01-09 08:56 – Jean MAURICE : Re: Invalid Group By
Back to top
Invalid Group By

Author: jerry foote

Posted: 2014-01-03 19:49:43   Link

"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

---

_______________________________________________

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/98F0C5EB42D447A9AC1D5ABEA2F58F1B@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.

©2014 jerry foote
Back to top
RE: Invalid Group By

Author: Darren

Posted: 2014-01-03 21:05:15   Link

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-bounces@leafe.com] On Behalf Of jerry

foote

Sent: Saturday, 4 January 2014 12:50 PM

To: profoxtech@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/000c01cf08f9$cbff7870$63fe6950$@ozemail.com.au

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

©2014 Darren
Back to top
Re: Invalid Group By

Author: Stephen Russell

Posted: 2014-01-03 21:24:32   Link

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 <foxdev@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-bounces@leafe.com] On Behalf Of jerry

> foote

> Sent: Saturday, 4 January 2014 12:50 PM

> To: profoxtech@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/CAJidMYLZirz4MCZHt1FerYk8cb_pATkNyqr7dbB2MzYXLCW4dQ@mail.gmail.com

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

©2014 Stephen Russell
Back to top
RE: Invalid Group By

Author: jerry foote

Posted: 2014-01-03 21:37:44   Link

Thank Guys

Jerry

-----Original Message-----

From: ProFox [mailto:profox-bounces@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 <foxdev@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-bounces@leafe.com] On Behalf Of jerry

> foote

> Sent: Saturday, 4 January 2014 12:50 PM

> To: profoxtech@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/4BE930CF62B84B98AF85EC1C7CCB9502@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.

©2014 jerry foote
Back to top
Re: Invalid Group By

Author: Rafael Copquin

Posted: 2014-01-04 08:01:20   Link

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-bounces@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 <foxdev@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-bounces@leafe.com] On Behalf Of jerry

>> foote

>> Sent: Saturday, 4 January 2014 12:50 PM

>> To: profoxtech@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.5020608@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.

©2014 Rafael Copquin
Back to top
RE: Invalid Group By

Author: jerry foote

Posted: 2014-01-04 08:22:18   Link

Thank you Rafael that was what I what I was looking for.

Jerry

<-----Original Message-----

<From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Rafael Copquin

<Sent: Saturday, January 04, 2014 8:01 AM

<To: profox@leafe.com

<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:profox-bounces@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 <foxdev@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-bounces@leafe.com] On Behalf Of jerry

>> foote

>> Sent: Saturday, 4 January 2014 12:50 PM

>> To: profoxtech@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/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.

©2014 jerry foote
Back to top
Re: Invalid Group By

Author: Stephen Russell

Posted: 2014-01-09 08:46:03   Link

On Sat, Jan 4, 2014 at 8:22 AM, jerry foote <jerryf@footegroup.com> wrote:

> Thank you Rafael that was what I what I was looking for.

>

> ---------------

>

Setting behavior to "fix" the problem is not going to work outside of VFP

data. Instead just follow the simple rule that everything in your select

list of fields that is not an aggregate needs to become your group by list.

Then as you work with other backends over time you are doing it correctly.

--

Stephen Russell

Sr. Analyst

Ring Container Technology

Oakland TN

901.246-0159 cell

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

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/CAJidMYJnvtOptFwi=bGngXBgD3iFmqgJK+U5md_ax-2F5dfFUg@mail.gmail.com

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

©2014 Stephen Russell
Back to top
Re: Invalid Group By

Author: Jean MAURICE

Posted: 2014-01-09 08:56:04   Link

and I think that in some very rare cases, complex SQL queries results with VFP9

differ from the same queries in VFP6 even if enginebehavior is set to 70. But I

have no example to show.

The Foxil

_______________________________________________

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/52CEB884.4050609@wanadoo.fr

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

©2014 Jean MAURICE