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


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