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
 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: 289127 # Views: 44 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 3:09:19 PM         
   


Unfortunately, even the following does not works for me:

SELECT curLedger.iOrder				AS iOrder ;
		, curLedger.iPID			AS iPID ;
		, curLedger.iGID			AS iGID ;
		, MAX(curLedger.tDt)		AS tDt ;
		, MAX(curLedger.iAcctID)	AS iAcctID ;
		, MAX(curLedger.bDebit)		AS bDebit ;
		, MAX(curLedger.bCredit)	AS bCredit ;
		, MAX(curLedger.cSource)	AS cSource ;
		, curLedger.mNarration		AS mNarration ;
		, MAX(curLedger.cDocBk)		AS cDocBk ;
		, MAX(curLedger.iDocNo)		AS iDocNo ;
		, MAX(curLedger.tDocDt)		AS tDocDt ;
		, MAX(curLedger.iDocAcctID)	AS iDocAcctID ;
		, SUM(curLedger.bAdjDebit)	AS bAdjDebit ;
		, SUM(curLedger.bAdjCredit)	AS bAdjCredit ;
		, MAX(curLedger.iOSAcctID)	AS 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.mNarration ;
	INTO CURSOR curOSAccount


> 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



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