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

omsai wrote:

> 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.

The non-aggregated columns in your SELECT are not ALL in the GROUP BY clause. And you have a column named SORT which is an Oracle keyword

SELECT keyword
FROM v$reserved_words
WHERE keyword like 'SO%';

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)