7 Replies Latest reply: Apr 30, 2008 6:08 AM by John Stegeman RSS

    Materialized Views Union ALL

    628824
      Sample SQL Query for Creating View

      /*
      CREATE MATERIALIZED VIEW TRIAL2
      PARALLEL 4
      BUILD IMMEDIATE
      REFRESH COMPLETE
      ENABLE QUERY REWRITE
      AS */
      select maa.INVENTORY_ITEM_ID,maa.ORGANIZATION_ID,maa.SR_INSTANCE_ID from msc_atp_rules ma,
                msc_atp_assignments maa
      where      maa.assignment_type = 3
      AND          maa.ATP_RULE_ID = ma.RULE_ID
      UNION ALL
      select maa.INVENTORY_ITEM_ID,maa.ORGANIZATION_ID,maa.SR_INSTANCE_ID from msc_atp_rules ma,
                msc_atp_assignments maa
      where      maa.assignment_type =3
      AND          maa.ATP_RULE_ID = ma.RULE_ID

      Test Syntax

      The SQL syntax is valid, however the query is invalid or uses functionality that is not supported.
      Declarative query support does not currently include UNION, INTERSECT or MINUS

      As a workaround, I created this view in the database and imported it onto jdev. This process worked totally fine. However, the Test Syntax still gives the same error, which means no modifications can be made on Jdev.

      Is my conclusion right, or am I missing some procedures that might get the UNIONs working?

      Thanks
      Rajiv