?
Solved

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

Posted on 2004-11-03
3
Medium Priority
?
1,352 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:jbauer22
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
ID: 12490729
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
 
LVL 2

Author Comment

by:jbauer22
ID: 12493832
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12501537
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question