Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
ORA-00979
51 views
Skip to first unread message
Tim Slattery
Oct 24, 2012, 3:23:21 PM10/24/12
to
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?
--
Tim Slattery
Slatt...@bls.gov
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?
--
Tim Slattery
Slatt...@bls.gov
joel garry
Oct 24, 2012, 3:42:27 PM10/24/12
to
Perhaps the index_date not being in a group by?
Maybe you need a subquery that has the select for the max date in it.
jg
--
@home.com is bogus.
EXT4 Data Corruption Bug Hits Stable Linux Kernels
http://www.phoronix.com/scan.php?page=news_item&px=MTIxNDQ
Geoff Muldoon
Oct 24, 2012, 5:24:44 PM10/24/12
to
Slatt...@bls.gov says...
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,
GM
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,
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;
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,
GM
Tim Slattery
Oct 25, 2012, 12:54:52 PM10/25/12
to
Geoff Muldoon <geoff....@trap.gmail.com> wrote:
>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.
Thanks, looks like that's it.
>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.
I'm converting an application from Sybase to Oracle. The SQL statement
I posted ran perfectly fine in Sybase. Different SQL dialects, I
suppose.
--
Tim Slattery
Slatt...@bls.gov
joel garry
Oct 25, 2012, 7:51:37 PM10/25/12
to
On Oct 25, 9:54 am, Tim Slattery <Slatter...@bls.gov> wrote:
> Slatter...@bls.gov
Hmmm, yes, this thing says it's a t-sql extension to the standard (and
implies the Oracle one is also a different extension to the standard,
though I haven't thought it through):
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug122.htm
v. http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2130020
http://flickrhivemind.net/
Hmmm, yes, this thing says it's a t-sql extension to the standard (and
implies the Oracle one is also a different extension to the standard,
though I haven't thought it through):
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug122.htm
v. http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2130020
http://flickrhivemind.net/
Peter Nilsson
Oct 29, 2012, 8:37:27 PM10/29/12
to
On Oct 25, 8:24 am, Geoff Muldoon <geoff.muld...@trap.gmail.com>
wrote:
> Slatter...@bls.gov says...
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
> If you only want the row for MAX(index_date) then you should SELECT
^^^^^^^
You're assuming there is only one such row.
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
wrote:
> Slatter...@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
> >
> > 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?
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.
index_date, price, currency_code, price_basis, not_traded,
price_estimated,
> If you only want the row for MAX(index_date) then you should SELECT
You're assuming there is only one such row.
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
0 new messages