File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error While Creating Materialized View

 
Pol Appan
Ranch Hand
Posts: 144
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am getting error ORA-22818: subquery expressions not allowed here while creating materialized view. I am using Oracle9i Enterprise Edition Release 9.2.0.1.0. Below pasted is my SQL Script.

Any help is highly appreciable.

Thanks

*********SQL************

select distinct(id),NAME,(select count(GRADE) from employees where
nationality like '%US%'and id=a.organization_id and grade=a.grade
group by ID,GRADE) US,(select count(GRADE) from employees where
nationality not like '%US%' and organization_id=a.organization_id and grade=a.grade
group by ORGANIZATION_ID,GRADE) NON_US,grade from employees a
where grade is not null
group by GRADE,ID,name
order by to_number(grade) desc
 
Sunil Dumpala
Greenhorn
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cosmos,

I think Oracle has a limitation that Subqueries cannot be used in Materialized View creations. I tried to change you create statement as following, I haven't tried this on any database so I am not sure if it would work or not.


select distinct(a.id), a.NAME,
US.cnt1,
NON_US.cnt2,
a.grade
from employees a,
(select count(GRADE) cnt1 from employees where nationality like '%US%'and id=a.organization_id
and grade=a.grade group by ID,GRADE) US,
(select count(GRADE) cnt2 from employees where nationality not like '%US%'
and organization_id=a.organization_id and grade=a.grade
group by ORGANIZATION_ID,GRADE) NON_US,
where a.grade is not null
group by GRADE,ID,name
order by to_number(a.grade) desc


-Sunil
 
Pol Appan
Ranch Hand
Posts: 144
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry for asking, but the query is not working, could you kindly look into it.

Thanks and appreciated.
 
Sunil Dumpala
Greenhorn
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did the query failed with an error or was the output not as expected. If there was an error please let me know what the error was.

-Sunil
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic