?
Solved

error on "CREATE MATERIALIZED VIEW"

Posted on 2007-10-17
24
Medium Priority
?
1,018 Views
Last Modified: 2013-12-19
I tried to create this materialized view:

CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyyy 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_attivo
            where x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_passiva
            and x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            ) AS classe_costo,
            (select max(cost_cat_attiva) from efm_cost_cat_passivo_attivo
            where x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            and x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_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(SYSDATE)) - 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_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

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!
0
Comment
Question by:dba_raf
  • 9
  • 9
  • 4
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20093237
the NEXT part is not a date, but a string containing the expression:

CREATE MATERIALIZED VIEW mv_cost_est
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
NEXT 'sysdate + 60/86400'
disable QUERY REWRITE
AS
SELECT
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20093273
the start with clause is also odd.
It's converting sysdate into a date


START WITH sysdate  
0
 

Author Comment

by:dba_raf
ID: 20093432
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20093593
>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 ...
0
 

Author Comment

by:dba_raf
ID: 20093796
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_attivo
            where x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_passiva
            and x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            ) AS classe_costo,

            (select max(cost_cat_attiva) from efm_cost_cat_passivo_attivo
            where x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            and x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_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(SYSDATE)) - 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?





0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20093845
can you run the query apart? maybe put the query into a normal view (and let the mview use that view)?
0
 

Author Comment

by:dba_raf
ID: 20094013
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094035
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094041
did you get the error creating the normal view
or in the mv selecting from the normal view?


0
 

Author Comment

by:dba_raf
ID: 20094407
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_attivo
            where x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_passiva
            and x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            ) AS classe_costo,
            (select max(cost_cat_attiva) from efm_cost_cat_passivo_attivo
            where x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva
            and x.cost_class_id = efm_cost_cat_passivo_attivo.cost_class_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(SYSDATE)) - 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_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


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
0
 

Author Comment

by:dba_raf
ID: 20094414
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094449
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20094458
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;

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094520
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
0
 

Author Comment

by:dba_raf
ID: 20094534
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
0
 

Author Comment

by:dba_raf
ID: 20094571
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094586
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?
0
 

Author Comment

by:dba_raf
ID: 20094653
my oracle version is 9.2.0.1.0
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094668
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_attivo
         WHERE x.cost_class_id =
                   efm_cost_cat_passivo_attivo.cost_class_passiva
           AND x.cost_cat_id = efm_cost_cat_passivo_attivo.cost_cat_passiva)
                                                              AS classe_costo,
       (SELECT MAX(cost_cat_attiva)
          FROM efm_cost_cat_passivo_attivo
         WHERE x.cost_cat_id =
                    efm_cost_cat_passivo_attivo.cost_cat_passiva
           AND x.cost_class_id =
                                efm_cost_cat_passivo_attivo.cost_class_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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20094772
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_attiva) 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_attivo 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
0
 

Author Comment

by:dba_raf
ID: 20108520
I tried this:

CREATE MATERIALIZED VIEW MV_EFM_ST1
BUILD IMMEDIATE
REFRESH complete
START WITH to_date(sysdate,'dd/mm/yyyy 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_attiva) 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_attivo 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?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 20108774
Which divisor is 0?

you'll get that if
 x.date_est_end = x.date_est_start

So you can exclude those rows (if that's appropriate)
WHERE wr.date_est_end != wr.date_est_start

or you can throw in an artificial dummy value
so you're dividing by a non-zero amount.  For example adding 1 second
  (x.date-est_end - x.date_est_start + 1/86400)

or

You can do a case statement  
CASE WHEN  x.date_est_end = x.date_est_start
              THEN   <do whatever you need it to do>
        ELSE
              <do the current calculation>
END
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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

862 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