2 Replies Latest reply: May 3, 2012 1:35 PM by 862270 RSS

    Outer join issue

    862270
      Hi All,

      I'm currently trying to create a MV using Fast refresh method.
      and I'm getting the following error:

      ORA-12052:
           cannot fast refresh materialized view string.string
      Cause:      Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns.
      Action:      Specify the FORCE or COMPLETE option. If this error is got during creation, the materialized view definition may have be changed. Refer to the documentation on materialized views.

      I'm trying to create the Unique constraints required. Could anybody help me with this?. Which are the "inner tables of an outer join"?

      Thi is the MV script:

      CREATE MATERIALIZED VIEW MVIEW1
      USING INDEX
      REFRESH ON DEMAND FAST
      WITH PRIMARY KEY
      DISABLE QUERY REWRITE AS
      SELECT fp.FINAL_AMOUNTFIXED,
      fp.FINAL_FPRATE,
      fp.updated_rate,
      fp.gsd_rate,
      fp.nongsd_rate,
      fp.AMT_CD,
      fp.AMT_CD_ADJ1,
      fp.AMT_CD_ADJ2,
      fp.AMT_LC,
      fp.AMT_LC_ADJ1,
      fp.AMT_LC_ADJ2,
      fp.ASSIGNMENT_STATUS,
      fp.CURRENCY,
      fp.DOLLARS,
      fp.FORECAST_REVENUE_STATUS,
      fp.GUID,
      NVL(fp.HOURS,0) HOURS,
      fp.ID,
      fp.JOB_LEVEL,
      fp.PJR_AMT_CD,
      fp.PJR_HOURS_VALUE,
      fp.PJR_LC,
      fp.PJR_RATE,
      fp.PJR_RATE_VALUE,
      fp.PROJECT_COST_CENTER,
      fp.PROJECT_COST_CENTERTXT,
      NVL(cust.project_manager,fp.project_manager) project_manager,
      NVL(cust.project_name,fp.project_name) project_name,
      fp.PROJECT_NUMBER,
      fp.PROJECT_ORGANIZATION,
      NVL(cust.project_status,fp.project_status) project_status,
      NVL(cust.project_type,fp.project_type) project_type,
      NVL(fp.RATE,0) Rate,
      fp.RATE_ADJ1,
      fp.RATE_ADJ2,
      fp.RATE_LEVEL,
      fp.REPORT_DATE_ID,
      fp.REPORT_DESCRIPTION,
      fp.RESOURCE_COST_CENTER,
      fp.RESOURCE_COST_CENTERTXT,
      fp.RESOURCE_COUNTRY,
      fp.RESOURCE_NAME,
      fp.RESOURCE_ORGANIZATION,
      fp.RESOURCE_TYPE,
      fp.REVENUECOST_TYPE,
      fp.ROLE_END_DATE,
      fp.ROLE_NAME,
      fp.ROLE_START_DATE,
      fp.TAB,
      fp.TASK_NUMBER,
      fp.TPRATE_TP,
      fp.WAR,
      fp.WE_DATE_ID,
      fp.WEEK,
      fp.WORK_TYPE,
      fp.PJR_FF_DIFF_MONTH,
      fp.PJR_FF_DIFF_QTR,
      NVL(fp.PJR_WOW,0) WOW,
      fp.PJR_WOW_HRS,
      fp.REMOVED_BY_ADJUSTMENT,
      fp.PICK_FOR_WRITEBACK,
      fp.DRAFT_REV_NUM,
      fp.DRAFT_INV_NUM,
      NVL(pca.country,fp.PROJECT_COUNTRY) Country,
      NVL(cust.customer_id,NVL(fp.CUSTOMER_NUMBER,'TBD')) Customer_ID,
      NVL(cust.CUSTOMER_NAME,NVL(fp.CUSTOMER_NAME,'TBD')) Customer_Name ,
      fp.ITEM_DATE,
      fp.REVENUE_CC,
      fp.EXPLANATION,
      fp.REPORT,
      fp.REPORT_ADJUSTMENT,
      fp.REPORT_ADJ1,
      fp.REPORT_ADJ2,
      fp.JE_DESCRIPTION,
      pca.REGION_HQ_TIERLAST_CODE,
      pca.MANAGER_FIELD_TIERLAST_CODE,
      pca.SPECIALTY_HQ_TIERLAST_CODE,
      pca.SECTOR_HQ_TIERLAST_CODE,
      rca.REGION_HQ_TIERLAST_CODE,
      rca.MANAGER_FIELD_TIERLAST_CODE,
      rca.SPECIALTY_HQ_TIERLAST_CODE,
      rca.SECTOR_HQ_TIERLAST_CODE,
      pca.VERTICALS_SUB_LOB,
      pca.MISC_GROUP ,
      rca.VERTICALS_SUB_LOB,
      rca.MISC_GROUP ,
      fp.gl_date,
      fp.pjr_wow_rate ,
      fp.created_by,
      fp.created_on,
      NVL(fp.TOTAL_FUNDING,0) TOTAL_FUNDING,
      NVL(fp.INCEPTION_TO_BURN,0) INCEPTION_TO_BURN,
      NVL(fp.AVAILABLE_FUNDING,0) AVAILABLE_FUNDING,
      NVL(fp.FORECAST_AMT,0) FORECAST_AMT,
      cust.pop_date,
      fp.COMMENTS_WOW_PROJ,
      fp.COMMENTS_PAPER_CONFIRM,
      rep.LOOKUP_CODE,
      rep.NAC_SORTING1,
      fp.ACTION_TO_CLOSE,
      NVL(fp.NEGATIVE_BACKLOG,0) NEGATIVE_BACKLOG,
      fp.SALES_REP,
      fp.FORECAST_DATE,
      pca.NAC_RES_TYPE_3,
      rca.NAC_RES_TYPE_3
      FROM FACT_PJR fp,
      COSTCENTER_ATTRIBUTES pca ,
      COSTCENTER_ATTRIBUTES rca,
      wb_customer_lookups cust,
      XXAPX_LOOKUPS rep
      WHERE fp.revenue_cc = pca.LEAF_CC(+)
      AND fp.RESOURCE_COST_CENTER = rca.LEAF_CC(+)
      AND fp.project_number = cust.project_number(+)
      AND fp.REPORT_ADJ2 = rep.VALUE(+)
      AND rep.LOOKUP_TYPE = 'NAC_WB_PHASE2';
        • 1. Re: Outer join issue
          Barbara Boehmer
          It would help if you could provide create table statements for the following tables, so that we could reproduce the problem:

          FACT_PJR
          COSTCENTER_ATTRIBUTES
          COSTCENTER_ATTRIBUTES
          wb_customer_lookups
          XXAPX_LOOKUPS

          I should also point out that, unless there are objects involved, this question belongs in the SQL and PL/SQL forum, not the objects forum.

          Edited by: Barbara Boehmer on May 3, 2012 7:16 AM
          • 2. Re: Outer join issue
            862270
            Ok, thank you.

            I'll make this question on that section.

            Regards,

            Belen