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_pe riod, 1, a.posted_total_amt, 0)) PD1,
sum(decode(a.accounting_pe riod, 2, a.posted_total_amt, 0)) PD2,
sum(decode(a.accounting_pe riod, 3, a.posted_total_amt, 0)) PD3,
sum(decode(a.accounting_pe riod, 4, a.posted_total_amt, 0)) PD4,
sum(decode(a.accounting_pe riod, 5, a.posted_total_amt, 0)) PD5,
sum(decode(a.accounting_pe riod, 6, a.posted_total_amt, 0)) PD6,
sum(decode(a.accounting_pe riod, 7, a.posted_total_amt, 0)) PD7,
sum(decode(a.accounting_pe riod, 8, a.posted_total_amt, 0)) PD8,
sum(decode(a.accounting_pe riod, 9, a.posted_total_amt, 0)) PD9,
sum(decode(a.accounting_pe riod, 10, a.posted_total_amt, 0)) PD10,
sum(decode(a.accounting_pe riod, 11, a.posted_total_amt, 0)) PD11,
sum(decode(a.accounting_pe riod, 12, a.posted_total_amt, 0)) PD12,
sum(decode(a.accounting_pe riod, 13, a.posted_total_amt, 0)) PD13,
sum(decode(a.accounting_pe riod, 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!
select a.execline, e.description, a.fiscal_year, a.mv_forecast,
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
sum(decode(a.accounting_pe
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe you can refresh MV in the night. Also you can visit this site:
http://asktom.oracle.com/pls/ask/f?p=4950:1:
http://asktom.oracle.com/pls/ask/f?p=4950:1:
ASKER