Materialized View Geometry Column [message #600775] |
Tue, 12 November 2013 06:59 |
saleh_binmasood
Messages: 30 Registered: October 2009
|
Member |
|
|
Good Day Guys,
I need to create a materialized view based on a table. Later i want to add a geometry column to this Mview. I was wondering is that possible to add the column later on ?
Earliest response is highly appreciated.
Thanks
-AS
|
|
|
|
|
Re: Materialized View Geometry Column [message #600837 is a reply to message #600833] |
Wed, 13 November 2013 00:54 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
What does that column belong to, then? You could add a "constant" column or create a function (which fetches data from another table), such asSQL> create or replace function f_dname (par_deptno in dept.deptno%type)
2 return dept.dname%type
3 is
4 retval dept.dname%type;
5 begin
6 select d.dname
7 into retval
8 from dept d
9 where d.deptno = par_deptno;
10
11 return (retval);
12 end;
13 /
Function created.
SQL> create materialized view mv_emp as
2 select e.empno,
3 e.ename,
4 f_dname(e.deptno) dname,
5 'hello world' hi
6 from emp e;
Materialized view created.
SQL> select * from mv_emp where rownum < 4;
EMPNO ENAME DNAME HI
---------- ---------- -------------------- -----------
7369 SMITH RESEARCH hello world
7499 ALLEN SALES hello world
7521 WARD SALES hello world
SQL>
Or, you could simply join two (or more) tables:SQL> create materialized view mv_emp as
2 select e.empno,
3 e.ename,
4 d.dname
5 from emp e,
6 dept d
7 where e.deptno = d.deptno;
Materialized view created.
SQL> select * from mv_emp where rownum < 4;
EMPNO ENAME DNAME
---------- ---------- --------------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
SQL>
Otherwise, I don't see how you could do that (unless you explain what you meant).
|
|
|