We help IT Professionals succeed at work.

ORA-00979 when creating materialized view

wscott30
wscott30 asked
on
984 Views
Last Modified: 2013-12-19
I am creating a materized view (MV) in Oracle 9.2.0.8.0 with this code...
<<
CREATE materialized view mv_manuscripts_by_georegion
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
select * from view_manuscripts_by_georegion
/
>>

I receive an 'ORA-00979: not a GROUP BY expression' message. The view I am selecting from to create the MV is coded thusly....
<<
select
r.coden as coden,
j.journal_id as journal,
r.region as region,
r.year as year,
sum(r.originally_submitted) as submissions,
sum(r.actual_published) as published,
   (select
    sum(rr.originally_submitted)
    from pprds.view_manuscripts_by_region rr
    where rr.coden = r.coden
    and rr.year = r.year
    ) totsub,
   (select
    sum(rr.actual_published)
    from pprds.view_manuscripts_by_region rr
    where rr.coden = r.coden
    and rr.year = r.year
    ) totpub
from
pprds.view_manuscripts_by_region r,
pprds.journal j
where j.coden = r.coden
group by r.coden, j.journal_id, r.region, year
/
>>

So...the view runs fine, but an MV with a 'select *' from that same view produces an error. Strange. Anybody know why this is happening?

Thanks.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
DrSqL,

Interesting reading, but after alot of expiramenting with different code changes, I still can't shake the 'ORA-00979' error.

Thanks,
Wallace
CERTIFIED EXPERT
Top Expert 2005

Commented:
Wallace,
   Are you on 9.2.0.4 patch 3 or above?  There's a bug fix in that patch.  Anything before that had a problem (979 error) with multiple references to the same table in a grouping MV.  Not always, but it could be the cause.  If you have metalink you should be able to find the bug and see if it applies to your version.

Good luck!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.