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 |
|
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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:56:30 CDT 2024
|