create materialized view test
tablespace netmeter
storage (initial 10M NEXT 10M PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT WITH ROWID
ENABLE QUERY REWRITE
as
select deptno, ename, null "location"
from emp
union all
select deptno, null, loc
from dept;

i am running the above code and getting error:

create materialized view test
*
ERROR at line 1:
ORA-30370: set operators are not supported in this context


Which clause did not allow set opeartor......

Thanks
-Harvinder


-----Original Message-----
From: Roger Shutte [mailto:Roger.[Email Address Removed] Friday, November 30, 2001 3:05 AM
To: Harvinder Singh
Cc: LazyDBA.com Discussion
Subject: Re: Union All and Materialized views


Harvinder,

you need to make sure your union alls work. Your columns need to match up,
ie order,
datatype etc.

here is an example from the scott/tiger schema (you will need to give scott
the
correct privileges first)


SQL>
SQL> create materialized view test as
2 select deptno, ename, null "location"
3 from emp
4 union all
5 select deptno, null, loc
6 from dept
7 /

Materialized view created.

SQL>
SQL>

Harvinder Singh wrote:

> Hi,
>
> We need to create the materialized view on query like:
> select * from table1
> union all
> select * from table2
> union all
> select * from tabl3
>
> we r getting error set operation not allows.....
> Is there any other way to create the materialized views based on union all
> queries???
>
> Thanks
> -Harvinder
>
> --------
> Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page