This discussion is archived
2 Replies Latest reply: May 3, 2012 11:35 AM by 862270 RSS

Outer join issue

862270 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Ok, thank you.

    I'll make this question on that section.

    Regards,

    Belen

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points