Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: Bhavbhuti Nathwani
  Where is Bhavbhuti Nathwani?
 Rajkot
 India
 Bhavbhuti Nathwani
 Tags
Subject: RE: Invalid GROUP BY - at my wits end
Thread ID: 289125 Message ID: 289128 # Views: 43 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 3:14:20 PM         
   


Is mNarration a memo field. You will have to remove it for the select as it cannot be part of the GROUP BY and also cannot be aggregated. Get a temp cursor without the memo, and then join that temp cursor with the original to get the memo field in the final cursor.

Regards
Tushar

> Hi all
>
> I have the following SELECT statement but it does not allow me to move further. I can always MAX() all the fields in there and maybe be able to make the GROUP BY much shorter but before I go that route I want to know why it want to stop me at all cost. curLedger is a free table created from FireBird backend data. I have tried numbers instead of fieldnames, fieldnames without the curLedger. prefix but to no avail. I have also removed the fields in the WHERE clause just in case that was the case, no joy. I have also tried to put them all in one line, na da
>
>
SELECT curLedger.iOrder ;
> 	  , curLedger.iPID ;
> 	  , curLedger.iGID ;
> 	  , curLedger.tDt ;
> 	  , curLedger.iAcctID ;
> 	  , curLedger.bDebit ;
> 	  , curLedger.bCredit ;
> 	  , curLedger.cSource ;
> 	  , curLedger.mNarration ;
> 	  , curLedger.cDocBk ;
> 	  , curLedger.iDocNo ;
> 	  , curLedger.tDocDt ;
> 	  , curLedger.iDocAcctID ;
> 	  , SUM(curLedger.bAdjDebit)  AS bAdjDebit ;
> 	  , SUM(curLedger.bAdjCredit) AS bAdjCredit ;
> 	  , curLedger.iOSAcctID ;
> 	FROM curLedger ;
> 	WHERE IIF(curLedger.bDebit > 0, curLedger.bDebit <> curLedger.bAdjDebit, ;
> 				IIF(curLedger.bCredit > 0, curLedger.bCredit <> curLedger.bAdjCredit, .T.)) ;
> 		AND curLedger.cSource <> 'C' ;
> 	GROUP BY curLedger.iPID ;
> 	  , curLedger.iGID ;
> 	  , curLedger.iOrder ;
> 	  , curLedger.tDt ;
> 	  , curLedger.iAcctID ;
> 	  , curLedger.bDebit ;
> 	  , curLedger.bCredit ;
> 	  , curLedger.cSource ;
> 	  , curLedger.cDocBk ;
> 	  , curLedger.iDocNo ;
> 	  , curLedger.tDocDt ;
> 	  , curLedger.iDocAcctID ;
> 	  , curLedger.iOSAcctID ;
> 	INTO CURSOR curOSAccount

>
>
> Please advise
>
> Thanks and regards
> Bhavbhuti

ENTIRE THREAD

Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/28/2010 2:59:24 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/28/2010 3:09:19 PM
RE: Invalid GROUP BY - at my wits end Posted by tushar @ 12/28/2010 3:14:20 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/28/2010 3:21:25 PM
RE: Invalid GROUP BY - at my wits end Posted by tushar @ 12/28/2010 3:33:31 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/28/2010 5:18:06 PM
RE: Invalid GROUP BY - at my wits end Posted by Anders Altberg @ 12/28/2010 6:21:27 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/29/2010 6:15:51 AM
RE: Invalid GROUP BY - at my wits end Posted by Anders Altberg @ 12/29/2010 5:25:35 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/29/2010 5:30:20 PM
RE: Invalid GROUP BY - at my wits end Posted by Suresh Adhikari @ 12/28/2010 5:20:26 PM
RE: Invalid GROUP BY - at my wits end Posted by Bhavbhuti Nathwani @ 12/28/2010 5:31:21 PM