0

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;
1

Interesting. It works without the column alias in the mview definition.

with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

       LVL
----------
         1
         2
         3
         4
         5

Create mview fails with column alias in definition:

create materialized view mv1(lvl) as
with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Error report -
ORA-12018: following error encountered during code generation for "BP"."MV1"
ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.

But it works without the column alias:

create materialized view mv1 as
with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Materialized view MV1 created.

I see no reason for your version not working, it may be a bug, but I could not find anything related to this on the support site of Oracle.

Above output is from version 18.4.

  • ( "CHANNEL_ID", "CATEGORY_ID", "CATEGORY_PARENT_ID", "CATEGORY_REC_ID", "CATEGORY_NAME", "CUSTOMER_ID", "DISCOUNT_PERCENT", "DISCOUNT_ORIG", "LVL" ) Removing this helped- But i don't understand why – Jay Jan 28 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.