Home » SQL & PL/SQL » SQL & PL/SQL » [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view (CORE 11.2.0.4.0 Production)
[Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663667] Wed, 14 June 2017 00:58 Go to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
Hi All,

I'm trying to create a materlized view that , but an below error is occurring

[Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause


CREATE MATERIALIZED VIEW dfd.NODE_DETAILS_MV (NODE_ID,RBSI_NODE,SF_NODE,NODE_SUPPLEMENTARY_FLAG,FULL_HIERARCHY,LEVEL_1_ID,LEVEL_1_ORGID,LEVEL_1_NAME,
LEVEL_2_ID,LEVEL_2_ORGID,LEVEL_2_NAME,LEVEL_3_ID,LEVEL_3_ORGID,LEVEL_3_NAME,LEVEL_4_ID,LEVEL_4_ORGID,
LEVEL_4_NAME,LEVEL_5_ID,LEVEL_5_ORGID,LEVEL_5_NAME,LEVEL_6_ID,LEVEL_6_ORGID,LEVEL_6_NAME,LEVEL_7_ID,
LEVEL_7_ORGID,LEVEL_7_NAME,LEVEL_8_ID,LEVEL_8_ORGID,LEVEL_8_NAME)
TABLESPACE SHARED_DATA
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          3M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
/* Formatted on 07/06/2017 14:10:04 (QP5 v5.163.1008.3004) */
WITH node_detail
     AS (SELECT nh.node_id,
                nh.start_node_id AS parent_node_id,
                n.hier_level_id AS parent_level,
                n.orgid AS parent_orgid,
                n.org_value AS parent_name
           FROM    nodes_hierarchy nh
                INNER JOIN
                   nodes n
                ON (nh.start_node_id = n.node_id))
SELECT DISTINCT
       n.node_id,
       CASE WHEN (nh.node_rbsi_flag > 0) THEN 'Y' ELSE 'N' END AS rbsi_node,
       external_utils_pkg.is_sf_node (n.node_id) AS sf_node,
       nh.node_supplementary_flag,
       np.node_path_with_suffix AS full_hierarchy,
       n1.parent_node_id AS level_1_id,
       n1.parent_orgid AS level_1_orgid,
       n1.parent_name AS level_1_name,
       n2.parent_node_id AS level_2_id,
       n2.parent_orgid AS level_2_orgid,
       n2.parent_name AS level_2_name,
       n3.parent_node_id AS level_3_id,
       n3.parent_orgid AS level_3_orgid,
       n3.parent_name AS level_3_name,
       n4.parent_node_id AS level_4_id,
       n4.parent_orgid AS level_4_orgid,
       n4.parent_name AS level_4_name,
       n5.parent_node_id AS level_5_id,
       n5.parent_orgid AS level_5_orgid,
       n5.parent_name AS level_5_name,
       n6.parent_node_id AS level_6_id,
       n6.parent_orgid AS level_6_orgid,
       n6.parent_name AS level_6_name,
       n7.parent_node_id AS level_7_id,
       n7.parent_orgid AS level_7_orgid,
       n7.parent_name AS level_7_name,
       n8.parent_node_id AS level_8_id,
       n8.parent_orgid AS level_8_orgid,
       n8.parent_name AS level_8_name
  FROM nodes n
       INNER JOIN nodes_hierarchy nh
          ON (n.node_id = nh.start_node_id) AND (n.node_id = nh.node_id)
       INNER JOIN node_paths np
          ON (n.node_id = np.node_id)
       LEFT JOIN node_detail n1
          ON (n.node_id = n1.node_id) AND (n1.parent_level = 1)
       LEFT JOIN node_detail n2
          ON (n.node_id = n2.node_id) AND (n2.parent_level = 2)
       LEFT JOIN node_detail n3
          ON (n.node_id = n3.node_id) AND (n3.parent_level = 3)
       LEFT JOIN node_detail n4
          ON (n.node_id = n4.node_id) AND (n4.parent_level = 4)
       LEFT JOIN node_detail n5
          ON (n.node_id = n5.node_id) AND (n5.parent_level = 5)
       LEFT JOIN node_detail n6
          ON (n.node_id = n6.node_id) AND (n6.parent_level = 6)
       LEFT JOIN node_detail n7
          ON (n.node_id = n7.node_id) AND (n7.parent_level = 7)
       LEFT JOIN node_detail n8
          ON (n.node_id = n8.node_id) AND (n8.parent_level = 8);

[Updated on: Wed, 14 June 2017 02:11] by Moderator

Report message to a moderator

Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663668 is a reply to message #663667] Wed, 14 June 2017 01:23 Go to previous messageGo to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
Hi,

still having same issue , while I have also created view as view_test for below query. . Appriciate for your quick suggestion.

SELECT nh.node_id,
                nh.start_node_id AS parent_node_id,
                n.hier_level_id AS parent_level,
                n.orgid AS parent_orgid,
                n.org_value AS parent_name
           FROM    nodes_hierarchy nh
                INNER JOIN
                   nodes n
                ON (nh.start_node_id = n.node_id)
Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663669 is a reply to message #663668] Wed, 14 June 2017 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-32034: unsupported use of WITH clause
 *Cause:  Inproper use of WITH clause because one of the following two reasons:
          1. nesting of WITH clause within WITH clause not supported yet
          2. For a set query, WITH clause can't be specified for a branch.
          3. WITH clause cannot be specified within parenthesis.
 *Action: correct query and retry
Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663670 is a reply to message #663669] Wed, 14 June 2017 02:14 Go to previous messageGo to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
I have tried to without using parenthisis. but getting below error

ORA-32036: unsupported case for inlining of query name in WITH clause
Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663672 is a reply to message #663670] Wed, 14 June 2017 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have tried

And what about SHOWING us what you tried? Or are we supposed to read over your shoulder?

The solution is simple: rewrite the query WITHOUT the WITH clause.

[Updated on: Wed, 14 June 2017 03:09]

Report message to a moderator

Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663685 is a reply to message #663672] Wed, 14 June 2017 07:24 Go to previous messageGo to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
Hi All,
can you please help to rewrite sql without with cluase.

[Updated on: Wed, 14 June 2017 07:41]

Report message to a moderator

Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663686 is a reply to message #663685] Wed, 14 June 2017 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just move the "node_detail" definition in WITH into the place(s) it is used in the query.
with t as (<subquery>
select * from t
is equivalent to
select * from (<subquery>)
Re: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view [message #663699 is a reply to message #663686] Wed, 14 June 2017 13:54 Go to previous message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
The with clause is not supported in MVIEWS. do what Michel suggested and take the query that is in the with and put it inline into the from clause
Previous Topic: ORA-00922: missing or invalid option - PROCEDURE with SPOOL
Next Topic: Help the newbie to do my first procedure
Goto Forum:
  


Current Time: Thu Dec 14 17:12:15 CST 2017

Total time taken to generate the page: 0.01288 seconds