Error While Creating Materialized View

[Pol Appan]

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]

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]

Sorry for asking, but the query is not working, could you kindly look into it.

Thanks and appreciated.

[Sunil Dumpala]

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