Home » SQL & PL/SQL » SQL & PL/SQL » Create Materialzed view using WITH clause
Create Materialzed view using WITH clause [message #438592] Mon, 11 January 2010 02:24 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi All,

How to create the Materialzed view using WITH clause? Using the below given code,
I'm not able to create the MV and it is disconnecing when trying to create the MV with the below code.

CREATE MATERIALIZED VIEW activity_mv
TABLESPACE DATA BUILD DEFERRED REFRESH FORCE ON DEMAND
WITH ROWID AS
WITH sum_sal  AS (SELECT deptno , SUM(sal)sal_sum  FROM  emp GROUP BY deptno ),
     cnt_emp  AS (SELECT deptno , COUNT(sal)cnt_sal FROM  emp GROUP BY deptno )
SELECT emp.deptno, emp.ename ,empno, sal
FROM  emp , sum_sal rec1 , cnt_emp rec2
WHERE emp.deptno = rec1.deptno
AND   rec1.deptno = rec2.deptno
AND   sal > (sal_sum/cnt_sal  )


Please let me know how to create it successfully and Oracle version is 9.2.0.7

Thanks...

[Updated on: Mon, 11 January 2010 02:43] by Moderator

Report message to a moderator

Re: Create Materialzed view using WITH clause [message #438598 is a reply to message #438592] Mon, 11 January 2010 02:43 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm not able to create the MV and it is disconnecing when trying to create the MV with the below code.

Use SQL*Plus and copy and paste your session.
Use CODE tags to post code not QUOTE tags.

Regards
Michel
Previous Topic: job automatically get offine (merged)
Next Topic: Email Notification
Goto Forum:
  


Current Time: Mon Sep 26 17:55:03 CDT 2016

Total time taken to generate the page: 0.08436 seconds