I have a strange problem creating a materialized view using the code generated by the export wizard.
I have created a materialized view and pasted following code into the sql query tab from the popup. Everything works fine and the view is there. But when i try to run this code in a simple sql query editor and press run script i get the following error message.
Database: 12c Release 1 Error code: ORA-32042 Description: recursive WITH clause must reference itself directly in one of the UNION ALL branches
What have i done wrong?
CREATE MATERIALIZED VIEW axchannel_customer_category_hierarchy_discount_mview ( "CHANNEL_ID", "CATEGORY_ID", "CATEGORY_PARENT_ID", "CATEGORY_REC_ID", "CATEGORY_NAME", "CUSTOMER_ID", "DISCOUNT_PERCENT", "DISCOUNT_ORIG", "LVL" ) AS WITH prejoined_discounts AS ( SELECT axchannel_category_hierarchy.*, axchannel_customer.id AS customer_id, axchannel_customer.customer_nr, axchannel_customer_category_discount.discount_percent FROM axchannel_category_hierarchy CROSS JOIN axchannel_customer left JOIN axchannel_customer_category_discount ON axchannel_category_hierarchy.channel_id = axchannel_customer_category_discount .channel_id AND axchannel_category_hierarchy.id = axchannel_customer_category_discount .category_id AND axchannel_customer_category_discount.customer_id = axchannel_customer .id WHERE axchannel_category_hierarchy.channel_id = axchannel_customer.channel_id ),foo ( channel_id, id, parent_id, category_rec_id, category_name, customer_id, discount_percent, discount_orig, lvl ) AS ( SELECT c.channel_id, c.id, c.parent_id, c.category_rec_id, c.category_name, c.customer_id, c.discount_percent, c.discount_percent AS discount_orig, 1 AS lvl FROM prejoined_discounts c WHERE c.parent_id IS NULL UNION ALL SELECT c.channel_id, c.id, c.parent_id, c.category_rec_id, c.category_name, c.customer_id, CASE WHEN c.discount_percent IS NULL THEN foo.discount_percent ELSE c.discount_percent END discount, c.discount_percent AS discount_orig, lvl + 1 FROM foo JOIN prejoined_discounts c ON c.channel_id = foo.channel_id AND c.parent_id = foo.id AND c.customer_id = foo.customer_id ) SELECT channel_id, id as category_id, parent_id as category_parent_id, category_rec_id, category_name, customer_id, discount_percent, discount_orig, lvl FROM foo;