aspose file tools*
The moose likes JDBC and the fly likes Error While Creating Materialized View Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error While Creating Materialized View" Watch "Error While Creating Materialized View" New topic
Author

Error While Creating Materialized View

Pol Appan
Ranch Hand

Joined: Aug 26, 2004
Posts: 144
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


Never take anything for granted because you never know when you will lose it
Sunil Dumpala
Greenhorn

Joined: Jul 28, 2005
Posts: 29
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

Joined: Aug 26, 2004
Posts: 144
Sorry for asking, but the query is not working, could you kindly look into it.

Thanks and appreciated.
Sunil Dumpala
Greenhorn

Joined: Jul 28, 2005
Posts: 29
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
 
subject: Error While Creating Materialized View