Re: ORA-00979

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Thu, 25 Oct 2012 08:24:44 +1100
Message-ID: <MPG.2af307abb214f69a9896b1_at_news.albasani.net>

Slattery_T_at_bls.gov says...
>
> I'm getting this error message:
>
> ORA-00979: not a GROUP BY expression ORA-06512: at
> "IPP.IPP_GETCURRENTPRICE", line 17 ORA-06512: at line 1
>
> Which points to this statement in a function:
>
> OPEN cv_1 FOR
> SELECT item_code,
> index_date,
> price,
> currency_code,
> price_basis,
> not_traded,
> price_estimated,
> no_change,
> is_valid
> FROM ipp_price
> WHERE item_code = v_code
> GROUP BY item_code
>
> HAVING index_date = MAX(index_date);
>
> The value being passed in for v_code is correct. What is it objecting
> to?

If you only want the row for MAX(index_date) then you should SELECT that, not use it in a HAVING clause. Then you will need to GROUP BY every other column that has not had an aggregation/function applied to it.

OPEN cv_1 FOR

    SELECT

        item_code,
        MAX(index_date) as index_date,
        price,
        currency_code,
        price_basis,
        not_traded,
        price_estimated,
        no_change,
        is_valid

    FROM ipp_price
    WHERE item_code = v_code
    GROUP BY

        item_code
        price,
        currency_code,
        price_basis,
        not_traded,
        price_estimated,
        no_change,
        is_valid;

GM Received on Wed Oct 24 2012 - 23:24:44 CEST