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!
LVL 2
jbauer22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
I think that the problem is caused by including the table "fis.tblexecline e" in a having clause:
...
         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    <-- here it is: table in the having clause
where  a.execline = e.exec_id(+)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbauer22Author Commented:
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?
0
Helena Markováprogrammer-analystCommented:
Maybe you can refresh MV in the night. Also you can visit this site:
http://asktom.oracle.com/pls/ask/f?p=4950:1:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.