[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2339
  • Last Modified:

How do I edit a Materialized View?

I have a client whose Oracle DBA has gone AWOL. There are a handful of MVs that are not working. If I copy the failing MVs to a SQL Editor (TOAD) and specifically name the schema, the query runs OK. We only need one last set of reports off the server as its functions are being transferred to another system.

I know absolutely nothing about Oracle. Is there a way for me to safely edit an MV? A GUI would be preferred.

Below is a sample of the SQL that works when the schema is included. I added the "ADMIN." to get the queries to run in TOAD.

select
    pc.CASE_NUMBER "CASE_NUMBER"
    ,case coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)
        when 1 then 'Monthly'
        when 3 then 'Quarterly'
        when 6 then 'Semi-Annual'
        when 12 then 'Annual'
    end "PREMIUM_MODE"
    ,pc.ADJUSTMENT_CODE
    ,pat.DESCRIPTION "ADJUSTMENT_DESCRIPTION"
    --,pc.PERIOD_APPLIED "START_PERIOD"
    --,add_months(last_day(pc.PERIOD_APPLIED),coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)-1) "END_PERIOD"
    ,case when pc.ADJUSTMENT_CODE = '8C' then pc.PERIOD_APPLIED else pc.BILL_PERIOD end "START_PERIOD"
    ,add_months(last_day(case when pc.ADJUSTMENT_CODE = '8C' then pc.PERIOD_APPLIED else pc.BILL_PERIOD end),coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)-1) "END_PERIOD"
    ,p.PLAN_NAME "HEALTH_PLAN"
    ,mpo.DEDUCTIBLE "DEDUCTIBLE"
    ,coin.DESCRIPTION "COINSURANCE"
    ,sum(pc.CASH_AMOUNT) "ADJUSTMENT"
from    
    ADMIN.PYMTCASH pc inner join (
        select PERIOD_APPLIED,CASE_NUMBER,max(RUN_NUMBER) RUN_NUMBER from ADMIN.PYMTCASH group by PERIOD_APPLIED,CASE_NUMBER
    ) pc_run on pc.PERIOD_APPLIED = pc_run.PERIOD_APPLIED and pc.CASE_NUMBER = pc_run.CASE_NUMBER and pc.RUN_NUMBER = pc_run.RUN_NUMBER
    left join (
        select bc.* from CURRENT_SNAPSHOT.BLRGCASE bc inner join (select INSERT_DATE,BILL_PERIOD,CASE_NUMBER,max(RUN_NUMBER) RUN_NUMBER from CURRENT_SNAPSHOT.BLRGCASE group by INSERT_DATE,BILL_PERIOD,CASE_NUMBER) run on bc.BILL_PERIOD = run.BILL_PERIOD and bc.CASE_NUMBER = run.CASE_NUMBER and bc.RUN_NUMBER = run.RUN_NUMBER and bc.INSERT_DATE = run.INSERT_DATE
    ) bc on case when pc.BILL_PERIOD = to_date('11/11/1111','mm/dd/yyyy') then pc.PERIOD_APPLIED else pc.BILL_PERIOD end = bc.BILL_PERIOD and pc.CASE_NUMBER = bc.CASE_NUMBER and pc.INSERT_DATE = bc.INSERT_DATE
    left join ADMIN.PYMTCASH_ADJUSTMENT_TYPE pat on pc.ADJUSTMENT_CODE = pat.CODE
    left join CURRENT_SNAPSHOT.CASETABLE ct on pc.CASE_NUMBER = ct.CASE_NUM
    left join ADMIN.PLANS p on coalesce(bc.HEALTH_PLAN,ct.HLTH_POLICY) = p.HEALTH_PLAN
    left join ADMIN.MED_PLAN_OPTIONS mpo on p.PLAN_ID = mpo.PLAN_ID
    left outer join (
        select
            MED_PLAN_CODES.CODE
            ,MED_PLAN_CODES.DESCRIPTION
        from
            ADMIN.MED_PLAN_CODES
        where
            ADMIN.MED_PLAN_CODES.CODE_TYPE = 'MED_COI'
    ) coin on mpo.COINSURANCE = coin.CODE
where
    --pc.BATCH_TYPE not in ('1','E','F','T','V')
    pc.ADJUSTMENT_CODE <> '8C'
group by
    pc.CASE_NUMBER
    ,case coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)
        when 1 then 'Monthly'
        when 3 then 'Quarterly'
        when 6 then 'Semi-Annual'
        when 12 then 'Annual'
    end
    ,pc.ADJUSTMENT_CODE
    ,pat.DESCRIPTION
    --,pc.PERIOD_APPLIED
    --,add_months(last_day(pc.PERIOD_APPLIED),coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)-1)
    ,case when pc.ADJUSTMENT_CODE = '8C' then pc.PERIOD_APPLIED else pc.BILL_PERIOD end
    ,add_months(last_day(case when pc.ADJUSTMENT_CODE = '8C' then pc.PERIOD_APPLIED else pc.BILL_PERIOD end),coalesce(to_number(bc.BILL_FREQUENCY),ct.BILL_FREQUENCY)-1)
    ,p.PLAN_NAME
    ,mpo.DEDUCTIBLE
    ,coin.DESCRIPTION
having sum(pc.CASH_AMOUNT) <> 0
0
cptntrip88
Asked:
cptntrip88
1 Solution
 
johnsoneSenior Oracle DBACommented:
You cannot "edit" a materialized view.  You can drop and recreate it.

However, if the only issue is missing owners on objects, then create public synonyms in their place and the materialized view should work.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now