Re: ORA-00979

From: Peter Nilsson <airia_at_acay.com.au>
Date: Mon, 29 Oct 2012 17:37:27 -0700 (PDT)
Message-ID: <c2907fc4-aa47-49c0-918e-4b642714711f_at_jj5g2000pbc.googlegroups.com>

On Oct 25, 8:24 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> Slatter..._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?

Your misunderstanding of the group by concept, the purpose of which is to aggragate over combinations of values from one or more columns (in general.) By selecting a column without aggragation, you're saying you want every row combination including values from that column.

So your query is saying give me a row for every combination of item_code,
index_date, price, currency_code, price_basis, not_traded, price_estimated,
no_change and is_valid, BUT only return one row for each item_code.

> If you only want the row for MAX(index_date) then you should SELECT

                   ^^^^^^^

You're assuming there is only one such row.

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

That will still return multiple rows for a given item_code if any of the
other columns differ for different index_date entries.

It is similar to...

    select p1.item_code,
           p1.index_date,
           p1.price,
           p1.currency_code,
           p1.price_basis,
           p1.not_traded,
           p1.price_estimated,
           p1.no_change,
           p1.is_valid
      from ipp_price p1
     where p1.item_code = v_code
       and p1.index_date =
             (select max(p2.index_date)
                from ipp_price p2
               where p2.item_code       = p1.item_code
                 and p2.price           = p1.price
                 and p2.currency_code   = p1.currency_code
                 and p2.price_basis     = p1.price_basis
                 and p2.not_traded      = p1.not_traded
                 and p2.price_estimated = p1.price_estimated
                 and p2.no_change       = p1.no_change
                 and p2.is_valid        = p1.is_valid);

Whereas I suspect the OP wants something more akin to...

    select p1.item_code,
           p1.index_date,
           p1.price,
           p1.currency_cde,
           p1.price_basis,
           p1.not_traded,
           p1.price_estimated,
           p1.no_change,
           p1.is_valid
      from ipp_price p1
     where p1.item_code = v_code
       and p1.index_date =
             (select max(p2.index_date)
                from ipp_price p2
               where p2.item_code = p1.item_code);

This too assumes that item_code and index_date form a unique key on ipp_price,
in which case, the following does the same but is generally more efficient...

    select item_code,

           max(index_date) keep (dense_rank last order by index_date) as index_date,

           max(price) keep (dense_rank last order by index_date) as price,

           max(currency_code) keep (dense_rank last order by index_date) as currency_cde,

           max(price_basis) keep (dense_rank last order by index_date) as price_basis,

           max(not_traded) keep (dense_rank last order by index_date) as not_traded,

           max(price_estimated) keep (dense_rank last order by index_date) as price_estimated,

           max(no_change)       keep (dense_rank last order by
index_date) as no_change,
           max(is_valid)        keep (dense_rank last order by
index_date) as is_valid
      from ipp_price
     where item_code = v_code
     group by item_code;

--
Peter

Received on Tue Oct 30 2012 - 01:37:27 CET