dba_raf
asked on
error on "CREATE MATERIALIZED VIEW"
I tried to create this materialized view:
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyy y hh24:mi:ss')
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
SELECT
efm_contratti.contratto_id ,
efm_commesse.dp_id,
x.wr_id,
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva
and x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
and x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva ) as cat_costo,
x.cost_est_total,
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE) ) > wr.date_est_start) or wr.pct_complete > 0 ) THEN
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE )) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start) ) * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1,
'EUR',
efm_commesse.ac_id,
project.project_id,
project.project_type,
x.status,
x.cost_est_total,
x.cost_total,
x.pct_complete,
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) then
round (((TO_DATE(LAST_DAY(SYSDAT E)) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start)*100), 2)
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO,
x.pct_ricarico,
x.commessa_id,
x.cost_class_id,
x.cost_cat_id
FROM
wr x, cf, efm_contratti, efm_commesse, project,wrbennonfattura
WHERE x.cf_id=cf.cf_id
AND x.commessa_id=efm_commesse .commessa_ id
AND x.contratto_id=efm_contrat ti.contrat to_id
AND x.project_id=project.proje ct_id
AND efm_commesse.status not in ('Closed')
and x.wr_id=wrbennonfattura.wr _id
and project.project_type in ('03','10')
AND x.cost_class_id is not null
but I get this error:
ORA-22818: subquery expressions not allowed here
How can I rewrite my query to avoid this error?
Thanks in advance!
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyy
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
SELECT
efm_contratti.contratto_id
efm_commesse.dp_id,
x.wr_id,
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv
where x.cost_class_id = efm_cost_cat_passivo_attiv
and x.cost_cat_id = efm_cost_cat_passivo_attiv
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv
where x.cost_cat_id = efm_cost_cat_passivo_attiv
and x.cost_class_id = efm_cost_cat_passivo_attiv
x.cost_est_total,
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE)
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1,
'EUR',
efm_commesse.ac_id,
project.project_id,
project.project_type,
x.status,
x.cost_est_total,
x.cost_total,
x.pct_complete,
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
round (((TO_DATE(LAST_DAY(SYSDAT
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO,
x.pct_ricarico,
x.commessa_id,
x.cost_class_id,
x.cost_cat_id
FROM
wr x, cf, efm_contratti, efm_commesse, project,wrbennonfattura
WHERE x.cf_id=cf.cf_id
AND x.commessa_id=efm_commesse
AND x.contratto_id=efm_contrat
AND x.project_id=project.proje
AND efm_commesse.status not in ('Closed')
and x.wr_id=wrbennonfattura.wr
and project.project_type in ('03','10')
AND x.cost_class_id is not null
but I get this error:
ORA-22818: subquery expressions not allowed here
How can I rewrite my query to avoid this error?
Thanks in advance!
the start with clause is also odd.
It's converting sysdate into a date
START WITH sysdate
It's converting sysdate into a date
START WITH sysdate
ASKER
if I try with NEXT 'sysdate + 60/86400' I get error: ORA-00984: column not allowed here
If I try with START WITH sysdate I get same error: ORA-22818: subquery expressions not allowed here
If I try with START WITH sysdate I get same error: ORA-22818: subquery expressions not allowed here
>If I try with START WITH sysdate I get same error: ORA-22818: subquery expressions not allowed here
is that error not from your actual query that the mv should execute?
can you check where in your create mv statement that error points to?
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
SELECT ...
is that error not from your actual query that the mv should execute?
can you check where in your create mv statement that error points to?
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
SELECT ...
ASKER
ORA-22818: subquery expressions not allowed points to:
) AS classe_costo
I think that problem is on these select:
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva
and x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
and x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva ) as cat_costo,
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE) ) > wr.date_est_start) or wr.pct_complete > 0 ) THEN
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE )) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start) ) * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) then
round (((TO_DATE(LAST_DAY(SYSDAT E)) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start)*100), 2)
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO
BECAUSE ARE SUBQUERY.
How can I rewrite my query?
) AS classe_costo
I think that problem is on these select:
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv
where x.cost_class_id = efm_cost_cat_passivo_attiv
and x.cost_cat_id = efm_cost_cat_passivo_attiv
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv
where x.cost_cat_id = efm_cost_cat_passivo_attiv
and x.cost_class_id = efm_cost_cat_passivo_attiv
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE)
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
round (((TO_DATE(LAST_DAY(SYSDAT
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO
BECAUSE ARE SUBQUERY.
How can I rewrite my query?
can you run the query apart? maybe put the query into a normal view (and let the mview use that view)?
ASKER
maybe put the query into a normal view (and let the mview use that view)?
I tried to create a normal view and create the mv with that view but I get same error.
I tried to create a normal view and create the mv with that view but I get same error.
yes, I just tested that, wrapping the query in a view and then
create my_temp_view as
<your query>;
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view
create my_temp_view as
<your query>;
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view
did you get the error creating the normal view
or in the mv selecting from the normal view?
or in the mv selecting from the normal view?
ASKER
create or replace view my_view
as
SELECT
efm_contratti.contratto_id ,
efm_commesse.dp_id,
x.wr_id,
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva
and x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv o
where x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva
and x.cost_class_id = efm_cost_cat_passivo_attiv o.cost_cla ss_passiva ) as cat_costo,
x.cost_est_total,
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE) ) > wr.date_est_start) or wr.pct_complete > 0 ) THEN
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE )) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start) ) * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) ) then
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1,
'EUR',
efm_commesse.ac_id,
project.project_id,
project.project_type,
x.status,
x.cost_est_total,
x.cost_total,
x.pct_complete,
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE)) then
round (((TO_DATE(LAST_DAY(SYSDAT E)) - wr.date_est_start) / (wr.date_est_end - wr.date_est_start)*100), 2)
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE)) then
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO,
x.pct_ricarico,
x.commessa_id,
x.cost_class_id,
x.cost_cat_id
FROM
wr x, cf, efm_contratti, efm_commesse, project,wrbennonfattura
WHERE x.cf_id=cf.cf_id
AND x.commessa_id=efm_commesse .commessa_ id
AND x.contratto_id=efm_contrat ti.contrat to_id
AND x.project_id=project.proje ct_id
AND efm_commesse.status not in ('Closed')
and x.wr_id=wrbennonfattura.wr _id
and project.project_type in ('03','10')
AND x.cost_class_id is not null
START WITH sysdate
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select *
from my_view
ORA-22818: subquery expressions not allowed here
My oracle version is 9.2.0
as
SELECT
efm_contratti.contratto_id
efm_commesse.dp_id,
x.wr_id,
(select max(cost_class_attiva) from efm_cost_cat_passivo_attiv
where x.cost_class_id = efm_cost_cat_passivo_attiv
and x.cost_cat_id = efm_cost_cat_passivo_attiv
) AS classe_costo,
(select max(cost_cat_attiva) from efm_cost_cat_passivo_attiv
where x.cost_cat_id = efm_cost_cat_passivo_attiv
and x.cost_class_id = efm_cost_cat_passivo_attiv
x.cost_est_total,
(SELECT CASE
WHEN status IN ('I','SA') and ( (TO_DATE(LAST_DAY(SYSDATE)
(case
when wr.pct_complete > 0 then
(wr.cost_est_total * wr.pct_complete /100 * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = wr.date_est_start and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * ((TO_DATE(LAST_DAY(SYSDATE
when (wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
when (wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
(wr.cost_est_total * (1 + pct_ricarico/100))
END)
WHEN status = 'Com' THEN cost_est_total * (1 + pct_ricarico/100)
WHEN status = 'CON' THEN cost_total * (1 + pct_ricarico/100)
WHEN status = 'BEN' THEN cost_total * (1 + pct_ricarico/100)
else
0
end
from wr where wr.wr_id=x.wr_id) as IMPORTO1,
'EUR',
efm_commesse.ac_id,
project.project_id,
project.project_type,
x.status,
x.cost_est_total,
x.cost_total,
x.pct_complete,
(SELECT CASE
WHEN status IN ('I','SA') THEN
(case
when wr.pct_complete = 0 and wr.date_est_start = wr.date_est_end and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
round (((TO_DATE(LAST_DAY(SYSDAT
when wr.pct_complete = 0 and wr.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete = 0 and wr.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
100
when wr.pct_complete > 0 then
wr.pct_complete
END)
WHEN status = 'Com' THEN 100
WHEN status = 'CON' THEN 100
WHEN status = 'BEN' THEN 100
else
0
end
from wr where wr.wr_id=x.wr_id) as SAL_CALCOLATO,
x.pct_ricarico,
x.commessa_id,
x.cost_class_id,
x.cost_cat_id
FROM
wr x, cf, efm_contratti, efm_commesse, project,wrbennonfattura
WHERE x.cf_id=cf.cf_id
AND x.commessa_id=efm_commesse
AND x.contratto_id=efm_contrat
AND x.project_id=project.proje
AND efm_commesse.status not in ('Closed')
and x.wr_id=wrbennonfattura.wr
and project.project_type in ('03','10')
AND x.cost_class_id is not null
START WITH sysdate
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select *
from my_view
ORA-22818: subquery expressions not allowed here
My oracle version is 9.2.0
ASKER
CREATE MATERIALIZED VIEW mv_EFM_ST1
BUILD IMMEDIATE
REFRESH complete
START WITH sysdate
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select *
from my_view
ORA-22818: subquery expressions not allowed here
My oracle version is 9.2.0
BUILD IMMEDIATE
REFRESH complete
START WITH sysdate
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select *
from my_view
ORA-22818: subquery expressions not allowed here
My oracle version is 9.2.0
I wonder if it's something more subtle.
Let's simplify.
Try this....
create or replace view my_temp_view as
select dummy, ( SELECT count(*) from user_objects) objcnt from dual;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
I just did that on 9.2.0.8.
I wasn't able to create the mv directly on the query with the select count sub-query, but wrapped in a view it worked.
Let's simplify.
Try this....
create or replace view my_temp_view as
select dummy, ( SELECT count(*) from user_objects) objcnt from dual;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
I just did that on 9.2.0.8.
I wasn't able to create the mv directly on the query with the select count sub-query, but wrapped in a view it worked.
again, we need to put ' ' around the NEXT expression...
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
select * from my_temp_view;
anyhow, what happens when you run:
select * from my_temp_view;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
select * from my_temp_view;
anyhow, what happens when you run:
select * from my_temp_view;
no, don't put quotes around the next
it's not like declaring a job interval.
well, it is, because that's where that goes, but you don't string-ify the parameter
I ran my statements above verbatim. They ran just fine.
Putting quotes in produces a 984 error
it's not like declaring a job interval.
well, it is, because that's where that goes, but you don't string-ify the parameter
I ran my statements above verbatim. They ran just fine.
Putting quotes in produces a 984 error
ASKER
I tried:
create or replace view my_temp_view as
select dummy, ( SELECT count(*) from user_objects) objcnt from dual;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
But also in this case I get: ORA-22818: subquery expressions not allowed here
create or replace view my_temp_view as
select dummy, ( SELECT count(*) from user_objects) objcnt from dual;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
But also in this case I get: ORA-22818: subquery expressions not allowed here
ASKER
If I run:
create or replace view my_temp_view as
select dummy, count(*) objcnt from dual
group by dummy;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
MV run correctly
create or replace view my_temp_view as
select dummy, count(*) objcnt from dual
group by dummy;
CREATE MATERIALIZED VIEW my_temp_mv
BUILD IMMEDIATE
REFRESH complete
START WITH SYSDATE
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
select * from my_temp_view;
MV run correctly
you didn't do a sub-query though. So, that's the problem.
What is your full version?
Are you patched up to 9.2.0.8?
What is your full version?
Are you patched up to 9.2.0.8?
ASKER
my oracle version is 9.2.0.1.0
Also I note that your two CASE subqueries aren't really necessary, you are sub-querying back into one of your main tables (wr). Based on the usage, WR_ID must be a unique identifier for the WR table.
So you can eliminate both of those selects and just put in the case itself. Your query will run faster too.
The next step (assuming upgrading to a version that allows the subqueries isn't feasible) is to replace the MAX subqueries with joins.
Before doing that though,
Test this to confirm my hypothesis about the WR table usage.
SELECT efm_contratti.contratto_id , efm_commesse.dp_id, x.wr_id,
(SELECT MAX(cost_class_attiva)
FROM efm_cost_cat_passivo_attiv o
WHERE x.cost_class_id =
efm_cost_cat_passivo_attiv o.cost_cla ss_passiva
AND x.cost_cat_id = efm_cost_cat_passivo_attiv o.cost_cat _passiva)
AS classe_costo,
(SELECT MAX(cost_cat_attiva)
FROM efm_cost_cat_passivo_attiv o
WHERE x.cost_cat_id =
efm_cost_cat_passivo_attiv o.cost_cat _passiva
AND x.cost_class_id =
efm_cost_cat_passivo_attiv o.cost_cla ss_passiva )
AS cat_costo,
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE) ) > x.date_est_start)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
)
THEN(x.cost_est_total *(1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ (x.date_est_end - x.date_est_start)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
)
THEN(x.cost_est_total *(1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
)
THEN(wr.cost_est_total
*(1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR', efm_commesse.ac_id, project.project_id, project.project_type,
x.status, x.cost_est_total, x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND(( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ (x.date_est_end - x.date_est_start)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id, x.cost_cat_id
FROM wr x, cf, efm_contratti, efm_commesse, project, wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL
So you can eliminate both of those selects and just put in the case itself. Your query will run faster too.
The next step (assuming upgrading to a version that allows the subqueries isn't feasible) is to replace the MAX subqueries with joins.
Before doing that though,
Test this to confirm my hypothesis about the WR table usage.
SELECT efm_contratti.contratto_id
(SELECT MAX(cost_class_attiva)
FROM efm_cost_cat_passivo_attiv
WHERE x.cost_class_id =
efm_cost_cat_passivo_attiv
AND x.cost_cat_id = efm_cost_cat_passivo_attiv
AS classe_costo,
(SELECT MAX(cost_cat_attiva)
FROM efm_cost_cat_passivo_attiv
WHERE x.cost_cat_id =
efm_cost_cat_passivo_attiv
AND x.cost_class_id =
efm_cost_cat_passivo_attiv
AS cat_costo,
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
)
THEN(x.cost_est_total *(1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ (x.date_est_end - x.date_est_start)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
)
THEN(x.cost_est_total *(1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
)
THEN(wr.cost_est_total
*(1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR', efm_commesse.ac_id, project.project_id, project.project_type,
x.status, x.cost_est_total, x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end > TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND(( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ (x.date_est_end - x.date_est_start)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end < TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end = TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id, x.cost_cat_id
FROM wr x, cf, efm_contratti, efm_commesse, project, wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL
Test this extensively, I "think" I've captured functional equivalency but I don't have your tables
to work with to check for sure.
Hopefully inline views will be allowed even though sub-queries aren't.
When you put this into an mv
SELECT z.contratto_id, z.dp_id, z.wr_id,
MAX(eccpa.cost_class_attiv a) classe_costo,
MAX(eccpa.cost_cat_attiva) cat_costo, z.cost_est_total, z.importo1,
z.eur, z.ac_id, z.project_id, z.project_type, z.status,
z.cost_est_total, z.cost_total, z.pct_complete, z.sal_calcolato,
z.pct_ricarico, z.commessa_id, z.cost_class_id, z.cost_cat_id
FROM (SELECT efm_contratti.contratto_id , efm_commesse.dp_id, x.wr_id,
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE) ) > x.date_est_start)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( wr.cost_est_total
* (1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR' eur, efm_commesse.ac_id, project.project_id,
project.project_type, x.status, x.cost_est_total,
x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND
(( ( TO_DATE
(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id,
x.cost_cat_id
FROM wr x,
cf,
efm_contratti,
efm_commesse,
project,
wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL) z,
efm_cost_cat_passivo_attiv o eccpa
WHERE z.cost_class_id = eccpa.cost_class_passiva
AND z.cost_cat_id = cost_cat_passiva
GROUP BY z.contratto_id,
z.dp_id,
z.wr_id,
z.cost_est_total,
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_est_total,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
to work with to check for sure.
Hopefully inline views will be allowed even though sub-queries aren't.
When you put this into an mv
SELECT z.contratto_id, z.dp_id, z.wr_id,
MAX(eccpa.cost_class_attiv
MAX(eccpa.cost_cat_attiva)
z.eur, z.ac_id, z.project_id, z.project_type, z.status,
z.cost_est_total, z.cost_total, z.pct_complete, z.sal_calcolato,
z.pct_ricarico, z.commessa_id, z.cost_class_id, z.cost_cat_id
FROM (SELECT efm_contratti.contratto_id
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( wr.cost_est_total
* (1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR' eur, efm_commesse.ac_id, project.project_id,
project.project_type, x.status, x.cost_est_total,
x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND
(( ( TO_DATE
(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id,
x.cost_cat_id
FROM wr x,
cf,
efm_contratti,
efm_commesse,
project,
wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL) z,
efm_cost_cat_passivo_attiv
WHERE z.cost_class_id = eccpa.cost_class_passiva
AND z.cost_cat_id = cost_cat_passiva
GROUP BY z.contratto_id,
z.dp_id,
z.wr_id,
z.cost_est_total,
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_est_total,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
ASKER
I tried this:
CREATE MATERIALIZED VIEW MV_EFM_ST1
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyy y hh24:mi:ss')
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
SELECT z.contratto_id,
z.dp_id,
z.wr_id,
MAX(eccpa.cost_class_attiv a) classe_costo,
MAX(eccpa.cost_cat_attiva) cat_costo,
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
FROM (SELECT efm_contratti.contratto_id , efm_commesse.dp_id, x.wr_id,
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE) ) > x.date_est_start)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR' eur, efm_commesse.ac_id, project.project_id,
project.project_type, x.status, x.cost_est_total,
x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND
(( ( TO_DATE
(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id,
x.cost_cat_id
FROM wr x,
cf,
efm_contratti,
efm_commesse,
project,
wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL) z, efm_cost_cat_passivo_attiv o eccpa
WHERE z.cost_class_id = eccpa.cost_class_passiva
AND z.cost_cat_id = eccpa.cost_cat_passiva
GROUP BY z.contratto_id,
z.dp_id,
z.wr_id,
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
when I run I get this error:
ORA-01476: divisor is equal to zero
How can I avoid thi error?
CREATE MATERIALIZED VIEW MV_EFM_ST1
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyy
NEXT sysdate + 60/86400
disable QUERY REWRITE
AS
SELECT z.contratto_id,
z.dp_id,
z.wr_id,
MAX(eccpa.cost_class_attiv
MAX(eccpa.cost_cat_attiva)
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
FROM (SELECT efm_contratti.contratto_id
x.cost_est_total,
CASE
WHEN x.status IN('I', 'SA')
AND ( (TO_DATE(LAST_DAY(SYSDATE)
OR x.pct_complete > 0
)
THEN(CASE
WHEN x.pct_complete > 0
THEN( x.cost_est_total
* x.pct_complete
/ 100
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end = x.date_est_start
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* ( ( TO_DATE(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
)
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
WHEN( x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
)
THEN( x.cost_est_total
* (1 + x.pct_ricarico / 100)
)
END
)
WHEN x.status = 'Com'
THEN cost_est_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'CON'
THEN cost_total *(1 + x.pct_ricarico / 100)
WHEN x.status = 'BEN'
THEN cost_total *(1 + x.pct_ricarico / 100)
ELSE 0
END AS importo1,
'EUR' eur, efm_commesse.ac_id, project.project_id,
project.project_type, x.status, x.cost_est_total,
x.cost_total, x.pct_complete,
CASE
WHEN x.status IN('I', 'SA')
THEN(CASE
WHEN x.pct_complete = 0
AND x.date_est_start = x.date_est_end
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end >
TO_DATE(LAST_DAY(SYSDATE))
THEN ROUND
(( ( TO_DATE
(LAST_DAY(SYSDATE))
- x.date_est_start
)
/ ( x.date_est_end
- x.date_est_start
)
* 100
),
2
)
WHEN x.pct_complete = 0
AND x.date_est_end <
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete = 0
AND x.date_est_end =
TO_DATE(LAST_DAY(SYSDATE))
THEN 100
WHEN x.pct_complete > 0
THEN x.pct_complete
END
)
WHEN x.status = 'Com'
THEN 100
WHEN x.status = 'CON'
THEN 100
WHEN x.status = 'BEN'
THEN 100
ELSE 0
END AS sal_calcolato,
x.pct_ricarico, x.commessa_id, x.cost_class_id,
x.cost_cat_id
FROM wr x,
cf,
efm_contratti,
efm_commesse,
project,
wrbennonfattura
WHERE x.cf_id = cf.cf_id
AND x.commessa_id = efm_commesse.commessa_id
AND x.contratto_id = efm_contratti.contratto_id
AND x.project_id = project.project_id
AND efm_commesse.status NOT IN('Closed')
AND x.wr_id = wrbennonfattura.wr_id
AND project.project_type IN('03', '10')
AND x.cost_class_id IS NOT NULL) z, efm_cost_cat_passivo_attiv
WHERE z.cost_class_id = eccpa.cost_class_passiva
AND z.cost_cat_id = eccpa.cost_cat_passiva
GROUP BY z.contratto_id,
z.dp_id,
z.wr_id,
z.importo1,
z.eur,
z.ac_id,
z.project_id,
z.project_type,
z.status,
z.cost_total,
z.pct_complete,
z.sal_calcolato,
z.pct_ricarico,
z.commessa_id,
z.cost_class_id,
z.cost_cat_id
when I run I get this error:
ORA-01476: divisor is equal to zero
How can I avoid thi error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyy
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
SELECT