Re: ORA-00979
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;
-- PeterReceived on Tue Oct 30 2012 - 01:37:27 CET