0 Replies Latest reply on Apr 1, 2015 6:53 AM by Rohit Patankar

    ORA-22818: subquery expressions not allowed here

    Rohit Patankar

      We have upgraded our environment from R12.0.6 to R12.2.4 version. During the upgrade we have identified that the materialize view creation steps has been changed. We have most of our MV's created in R12.0.6 version with subqueries  in the SQL used in MV creation. Now while recreating those in R12.2 version. we are getting error that subquery not allowed here in MV creation in R12.2 version. I would like to know if any one has encountered the same issue and what solution or workaround you have used to resolve this issue.

       

      Scenario :

       

       

      Scenario 1:- Connected to APPS user and executed the below Queries
      ==================================================================
      CREATE OR REPLACE VIEW AEPL_TEST6_V AS SELECT /*+ PARALLEL */ header_id ,(Select 'Rohit' from dual)a FROM oe_order_headers_all ooha WHERE 1 = 1;

      create or replace view AEPL_TEST6_MV# as select * from AEPL_TEST6_V;

      begin ad_zd_mview.upgrade('APPS','AEPL_TEST6_MV'); END;

      ERROR ----- ERROR at line 1: ORA-22818: subquery expressions not allowed here
      ORA-06512: at "APPS.AD_ZD_MVIEW", line 590
      ORA-06512: at "APPS.AD_ZD_MVIEW", line 707
      ORA-06512: at "APPS.AD_ZD_MVIEW", line 1127
      ORA-06512: at "APPS.AD_ZD_MVIEW", line 1863
      ORA-06512: at line 2