Link to home
Start Free TrialLog in
Avatar of jbauer22
jbauer22

asked on

Materialized View Error - Name is already used by an existing object

I am trying to compile a Materialized View but get the following error "ORA-00955: Name is already used by an existing object".  I'm using PL/SQL developer to compile the object.  It points me to the 3rd line from the bottom of the SQL and indicates something is wrong with the fis.tblexecline e table.  Here is the mv:



select a.execline, e.description, a.fiscal_year, a.mv_forecast,
       sum(decode(a.accounting_period, 1, a.posted_total_amt, 0)) PD1,
       sum(decode(a.accounting_period, 2, a.posted_total_amt, 0)) PD2,
       sum(decode(a.accounting_period, 3, a.posted_total_amt, 0)) PD3,
       sum(decode(a.accounting_period, 4, a.posted_total_amt, 0)) PD4,
       sum(decode(a.accounting_period, 5, a.posted_total_amt, 0)) PD5,
       sum(decode(a.accounting_period, 6, a.posted_total_amt, 0)) PD6,
       sum(decode(a.accounting_period, 7, a.posted_total_amt, 0)) PD7,
       sum(decode(a.accounting_period, 8, a.posted_total_amt, 0)) PD8,
       sum(decode(a.accounting_period, 9, a.posted_total_amt, 0)) PD9,
       sum(decode(a.accounting_period, 10, a.posted_total_amt, 0)) PD10,
       sum(decode(a.accounting_period, 11, a.posted_total_amt, 0)) PD11,
       sum(decode(a.accounting_period, 12, a.posted_total_amt, 0)) PD12,
       sum(decode(a.accounting_period, 13, a.posted_total_amt, 0)) PD13,
       sum(decode(a.accounting_period, 14, a.posted_total_amt, 0)) PD14
from   (select f.fiscal_year fiscal_year, p1.fcst || substr(p1.ppyy, 1, 2) mv_forecast,
                s.execline execline, f.accounting_period accounting_period,
                sum(f.posted_total_amt) posted_total_amt
         from   fis.fis_actual f, fis.tblsite s, fis.tblperiod p1
         where  f.execid = s.execid and f.product = s.product(+) and f.fiscal_year = p1.fyr and
                p1.fcst like 'F%' and p1.pd > 1 and
                f.accounting_period < (select pd from fis.tblperiod where ppyy = p1.ppyy)
         group  by f.fiscal_year, p1.fcst || substr(p1.ppyy, 1, 2), s.execline, f.accounting_period
         having sum(f.posted_total_amt) <> 0
         union
         select f2.fiscal_year fiscal_year, p2.fcst || substr(p2.ppyy, 1, 2) mv_forecast,
                p.execline execline, f2.accounting_period accounting_period,
                sum(f2.posted_total_amt) posted_total_amt
         from   fis.fis_actual f2, fis.tblproject p, fis.tblperiod p2
         where  f2.execid = p.execid and f2.project_id = p.project_id(+) and f2.fiscal_year = p2.fyr and
                p2.fcst like 'F%' and p2.pd > 1 and
                f2.accounting_period < (select pd from fis.tblperiod where ppyy = p2.ppyy)
         group  by f2.fiscal_year, p2.fcst || substr(p2.ppyy, 1, 2), p.execline, f2.accounting_period
         having sum(f2.posted_total_amt) <> 0
         union
         select f3.fiscal_year fiscal_year, p3.fcst || substr(p3.ppyy, 1, 2) mv_forecast,
                '2221' || substr(s1.execline, 5, 3) EXECLINE, f3.accounting_period accounting_period,
                sum(f3.posted_total_amt) posted_total_amt
         from   fis.fis_actual f3, fis.tblsite s1, fis.tblperiod p3
         where  f3.execid = s1.execid(+) and f3.product = s1.product(+) and f3.fiscal_year = p3.fyr and
                p3.fcst like 'F%' and p3.pd > 1 and
                f3.accounting_period < (select pd from fis.tblperiod where ppyy = p3.ppyy) and
                f3.execid = '2202' and f3.account <> '710375'
         group  by f3.fiscal_year, p3.fcst || substr(p3.ppyy, 1, 2), s1.execline, f3.accounting_period
         having sum(f3.posted_total_amt) <> 0
         union
         select f.fiscal_year fiscal_year, f.mv_forecast mv_forecast, s.execline execline,
                f.accounting_period accounting_period, sum(f.posted_total_amt) posted_total_amt
         from   fis.fis_forecast f, fis.tblsite s
         where  f.execid = s.execid and f.product = s.product(+)
         group  by f.fiscal_year, f.mv_forecast, s.execline, f.accounting_period
         having sum(f.posted_total_amt) <> 0
         union
         select f2.fiscal_year fiscal_year, f2.mv_forecast mv_forecast, p.execline execline,
                f2.accounting_period accounting_period, sum(f2.posted_total_amt) posted_total_amt
         from   fis.fis_forecast f2, fis.tblproject p
         where  f2.execid = p.execid and f2.project_id = p.project_id(+)
         group  by f2.fiscal_year, f2.mv_forecast, p.execline, f2.accounting_period
         having sum(f2.posted_total_amt) <> 0
         union
         select f3.fiscal_year fiscal_year, f3.mv_forecast mv_forecast,
                '2221' || substr(s1.execline, 5, 3) EXECLINE, f3.accounting_period accounting_period,
                sum(f3.posted_total_amt) posted_total_amt
         from   fis.fis_forecast f3, fis.tblsite s1
         where  f3.execid = s1.execid(+) and f3.product = s1.product(+) and f3.execid = '2202' and
                f3.account <> '710375'
         group  by f3.fiscal_year, f3.mv_forecast, s1.execline, f3.accounting_period
         having sum(f3.posted_total_amt) <> 0) a, fis.tblexecline e
where  a.execline = e.exec_id(+)
group  by a.fiscal_year, a.mv_forecast, a.execline, e.description



Help!
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jbauer22
jbauer22

ASKER

I don't know why but it is working now.  One issue we are having is the refresh seems to truncate the MV while it is processing.  This poses a big problem for us as we would like this MV to be used for reporting.  We can't afford for users to get blank reports during the refresh process.  Is there a way to keep the data while refresh is happening so that there is never a loss blank MV?
Maybe you can refresh MV in the night. Also you can visit this site:
http://asktom.oracle.com/pls/ask/f?p=4950:1: