Question: I lam trying to create a view
using the WITH clause and I am getting an ORA-3034 error:
ORA-32034: unsupported use of WITH
clause
How to I create a view using the WITH clause?
Answer: The
syntax is tricky but you can indeed create a view using the
WITH clause as shown in this example:
create or replace view
view1
as
with
q1
as
(select
*
from
emp
where
deptno=10)
select * from q1;
select * from
view1
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7782,CLARK,MANAGER,7839,6/9/1981,2450,,10
7839,KING,PRESIDENT,,11/17/1981,5000,,10
7934,MILLER,CLERK,7782,1/23/1982,1300,,10