"Group By" with "Order By Decode" in PLSQL causes "ORA-00979: not a GROUP BY expression"

From: omsai <pavan.sura_at_gmail.com>
Date: 9 Feb 2005 07:57:43 -0800
Message-ID: <1107964663.522932.228570@g14g2000cwa.googlegroups.com>

Hi,

I have spent enough time trying to fix this without much luck. Here is what happening. I have a oracle procedure where I am trying to use both Group By and Order By in the select query. The Order By has a Decode function as I am passing the order variable from the front end of the application.

The package where i have all my procedures executes fine but I get the error when i acutally call the procedure from the front end. The error doesn't show up if I donot have decode function and just use a single value. That sounds strange to me.

Here is the code block. It is just part of the procedure where i get the error.

---

OPEN v_cursor FOR
SELECT trs.ID TID, trs.Name SellerName,

	own.Name OwnerName,lkup.Name Status,
	COUNT (1) TransactionParcelCount,
	Sum(aqi.CURRENTOFFERAMOUNT) TotalOfferAmount,
	trs.LastUpdate LastUpdated
	FROM ACQUISITION aqi, TRANSACTIONS trs,
		Owner own , LOOKUP lkup
	WHERE lower(own.Name) LIKE''||lower(Searchstring)||'%'
		AND lkup.Category='TransactionStatus'
		AND lkup.ValueID = trs.StatusID
		AND trs.ID = aqi.TransactionID
		AND own.ID = trs.SellerID
	GROUP BY trs.ID,trs.Name,own.Name,lkup.Name,
		trs.LastUpdate
	ORDER BY decode
	(sort,'OwnerName',OwnerName,'TID',TID,


	'TransactionParcelCount',TransactionParcelCount,

	'TotalOfferAmount',TotalOfferAmount,'LastUpdated',

	LastUpdated,'STATUS',STATUS);

o_cursor := v_cursor;

---

I would greatly appreciate if some one can help me out.

Thanks,
Pavan K Sura. Received on Wed Feb 09 2005 - 09:57:43 CST