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