SQL group by error

TheMSsForum.com: The Microsoft Software Forum

the following command works fine in VFP 6, but won't work with VFP 9. I get
a "1807: SQL: group by clause is missing or invalid". What is wrong with
it?! (something silly I've missed I bet)

select Creditor.CreditorCd, Creditor.AddrRecID, ;
GetDisc(Creditor.PromptTerm) as Discount, ;
CredTran.CrTranID, TranDate, PayBy, Reference, CredTran.Amount, ;
CredTran.Outstandng, TotAuthAmt, PromptTerm, CredTran.Paid,
CredTran.TaxAmount, ;
00000.00 as DisAmount, 000000000.00 as AmtToPay, ;
00000000.00 as Pay, "Chq" as cPayType, ;
count(1) as LineCount ;
from CredTran inner join Creditor ;
on CredTran.AddrRecID = Creditor.AddrRecID ;
inner join TranLine ;
on TranLine.CrTranID = CredTran.CrTranID ;
where PayBy <= pdPayToDate .and. CredTran.Outstandng > 0 ;
and TotAuthAmt > 0 and TotAuthAmt <> CredTran.Paid ;
and OK2Alloc({}, CredTran.Outstandng, .T., CredTran.TranType,
CredTran.Status, .T.) ;
group by CredTran.CrTranID ;
order by PayBy ;
into table (lcFileName)



Grant
Top

Re: SQL group by error by Fred

Fred
Tue Mar 28 19:51:35 CST 2006

While it LOOKED LIKE it worked correctly in VFP6, it was actually returning
ambiguous results to you. VFP has brought the SQL more in line with the
standard. What you need to do is group by all non-agregate fields in your
field list, or you'll have to change the way you do the SELECT.

--
Fred
Microsoft Visual FoxPro MVP


"Grant" <grant_ahead@hotmail.com> wrote in message
news:%23jbSA8sUGHA.4452@TK2MSFTNGP12.phx.gbl...
> the following command works fine in VFP 6, but won't work with VFP 9. I
> get a "1807: SQL: group by clause is missing or invalid". What is wrong
> with it?! (something silly I've missed I bet)
>
> select Creditor.CreditorCd, Creditor.AddrRecID, ;
> GetDisc(Creditor.PromptTerm) as Discount, ;
> CredTran.CrTranID, TranDate, PayBy, Reference, CredTran.Amount, ;
> CredTran.Outstandng, TotAuthAmt, PromptTerm, CredTran.Paid,
> CredTran.TaxAmount, ;
> 00000.00 as DisAmount, 000000000.00 as AmtToPay, ;
> 00000000.00 as Pay, "Chq" as cPayType, ;
> count(1) as LineCount ;
> from CredTran inner join Creditor ;
> on CredTran.AddrRecID = Creditor.AddrRecID ;
> inner join TranLine ;
> on TranLine.CrTranID = CredTran.CrTranID ;
> where PayBy <= pdPayToDate .and. CredTran.Outstandng > 0 ;
> and TotAuthAmt > 0 and TotAuthAmt <> CredTran.Paid ;
> and OK2Alloc({}, CredTran.Outstandng, .T., CredTran.TranType,
> CredTran.Status, .T.) ;
> group by CredTran.CrTranID ;
> order by PayBy ;
> into table (lcFileName)
>
>
>
> Grant
>
>


Top

Re: SQL group by error by Grant

Grant
Tue Mar 28 23:27:59 CST 2006

Thanks for that...yes I see what is going on now. I think it was OK before
because if was only the count(1) that needed TranLine (for counting how many
lines for each CredTran).

--

Regards

Grant

"Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
news:OGFtJMtUGHA.5172@TK2MSFTNGP12.phx.gbl...
> While it LOOKED LIKE it worked correctly in VFP6, it was actually
> returning ambiguous results to you. VFP has brought the SQL more in line
> with the standard. What you need to do is group by all non-agregate
> fields in your field list, or you'll have to change the way you do the
> SELECT.
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Grant" <grant_ahead@hotmail.com> wrote in message
> news:%23jbSA8sUGHA.4452@TK2MSFTNGP12.phx.gbl...
>> the following command works fine in VFP 6, but won't work with VFP 9. I
>> get a "1807: SQL: group by clause is missing or invalid". What is wrong
>> with it?! (something silly I've missed I bet)
>>
>> select Creditor.CreditorCd, Creditor.AddrRecID, ;
>> GetDisc(Creditor.PromptTerm) as Discount, ;
>> CredTran.CrTranID, TranDate, PayBy, Reference, CredTran.Amount, ;
>> CredTran.Outstandng, TotAuthAmt, PromptTerm, CredTran.Paid,
>> CredTran.TaxAmount, ;
>> 00000.00 as DisAmount, 000000000.00 as AmtToPay, ;
>> 00000000.00 as Pay, "Chq" as cPayType, ;
>> count(1) as LineCount ;
>> from CredTran inner join Creditor ;
>> on CredTran.AddrRecID = Creditor.AddrRecID ;
>> inner join TranLine ;
>> on TranLine.CrTranID = CredTran.CrTranID ;
>> where PayBy <= pdPayToDate .and. CredTran.Outstandng > 0 ;
>> and TotAuthAmt > 0 and TotAuthAmt <> CredTran.Paid ;
>> and OK2Alloc({}, CredTran.Outstandng, .T., CredTran.TranType,
>> CredTran.Status, .T.) ;
>> group by CredTran.CrTranID ;
>> order by PayBy ;
>> into table (lcFileName)
>>
>>
>>
>> Grant
>>
>>
>
>


Top

Re: SQL group by error by Cindy

Cindy
Wed Mar 29 10:26:14 CST 2006

Hi Grant,

Just to add, you can use Set Enginebehavior to revert back to the earlier
SQL syntax rules. This was included so that people who had many apps with
lots of SQL could migrate them and they would still work, but of course the
intention was that they should eventually refactor the SQL to meet the new
VFP (ANSI) standard.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Grant" <grant_ahead@hotmail.com> wrote in message
news:%23EnmPGvUGHA.4156@TK2MSFTNGP10.phx.gbl...
> Thanks for that...yes I see what is going on now.


Top

Re: SQL group by error by Grant

Grant
Wed Mar 29 17:53:27 CST 2006

Yes, just found that out, whew!

Grant


"Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
news:%23XMxv40UGHA.5884@TK2MSFTNGP14.phx.gbl...
> Hi Grant,
>
> Just to add, you can use Set Enginebehavior to revert back to the earlier
> SQL syntax rules. This was included so that people who had many apps with
> lots of SQL could migrate them and they would still work, but of course
> the intention was that they should eventually refactor the SQL to meet the
> new VFP (ANSI) standard.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Grant" <grant_ahead@hotmail.com> wrote in message
> news:%23EnmPGvUGHA.4156@TK2MSFTNGP10.phx.gbl...
>> Thanks for that...yes I see what is going on now.
>
>


Top