title: ORA-01722: invalid number when creating materialized view
body: <p>I am creating a view and the same view converted into materialized view in the same system. But doing the same thing in another system I got error <code>ORA-01722: invalid number</code> when creating the materialized view. Why?</p>
  
  <pre><code>create materialized view MV_EMP_VALI 
   refresh complete with rowid start with SYSDATE+1/24 AS 
   (select * from V_CHA1);
  </code></pre>
  
  <p>View:-</p>
  
  <pre><code>CREATE OR REPLACE VIEW V_CHA1 AS(SELECT EMPNO,
         MONTHYEAR,
         to_number(SUM(CPFEMO)) AS EMOLUMENTS,
         to_number(SUM(CPEPF)) AS EMPPFSTATUARY,
         to_number(SUM(AEMO)) AS AEMO,
         to_number(SUM(APEPF)) AS APEPF,
         MAX(recsts) AS recsts
    FROM ((SELECT RECDATE,
                     (CASE WHEN (REPFEMOFLAG='N') THEN
                  round(NVL(trim(EMO), 0))
                  ELSE
                  round(NVL(REVISEMO, 0)) END ) as CPFEMO,
                  round(NVL(trim(EPF), 0)) AS CPEPF,
                  0 as AEMO,
                  0 as APEPF,
                  '' as recsts,
                  EMPNO
             FROM EMP_VALI
            WHERE EFLAG = 'Y' AND SFLAG = 'N' AND EMPNO IS NOT NULL and
                  RECDATE &gt;'01-Apr-2011')
          union all
          (SELECT NDT.RECDATE AS RECDATE,
                  sum(round(NVL(trim(NDT.EMO), 0))) as CPFEMO,
                  sum(round(NVL(trim(NDT.EPF), 0))) as CPEPF,
                  0 as AEMO,
                  0 AS APEPF,
                  NDT.EMPNO
             FROM EMP_VALI VAL, EMP_SUPP NDT
            WHERE VAL.EMPNO = NDT.EMPNO AND VAL.EFLAG = NDT.EFLAG AND
                  VAL.EFLAG = 'Y' AND VAL.SFLAG = 'Y' AND
                  NDT.SLIFLAG='N' and
                   VAL.EMPNO is not null and
                  NDT.RECDATE = VAL.RECDATE
             GROUP BY NDT.RECDATE, NDT.EMPNO) UNION ALL
          (SELECT DT.RECPAIDDATE AS RECDATE,
                  0           as CPFEMO,
                  0           as CPEPF,
                  sum(round(NVL(trim(DT.EMO), 0))) as AEMO,
                  sum(round(NVL(trim(DT.EPF), 0))) AS APEPF,
          max('') as recsts,
                  DT.EMPNO
             FROM EMP_VALI VAL, EMP_SUPP DT
            WHERE VAL.EMPNO = DT.EMPNO AND VAL.EFLAG = DT.EFLAG AND
                  VAL.EFLAG = 'Y' AND VAL.SFLAG = 'Y'  AND
                  VAL.EMPNO IS NOT NULL and dt.RECDATE=val.RECDATE AND DT.SFLAG IS NOT NULL AND DT.SFLAG not in ('N','F')
             GROUP BY DT.RECPAIDDATE, DT.EMPNO)UNION ALL
          (SELECT DT.RECPAIDDATE AS RECDATE,
        SUM((CASE
               WHEN (DT.ECR4FLAG = 'C') then
                round(NVL(trim(DT.EMO), 0))
               else
               0
             end)) as CPFEMO,
         sum((CASE
               WHEN DT.ECR4FLAG = 'C' then
                round(NVL(trim(DT.EPF), 0))
               else
                0
             end)) as CPEPF,
            sum((CASE
               WHEN DT.ECR4FLAG = 'A' then
                round(NVL(trim(DT.EMO), 0))
               else
                0
             end)) as AEMO,
         sum((CASE
               WHEN DT.ECR4FLAG = 'A' then
                round(NVL(trim(DT.EPF), 0))
               else
                0
             end)) as APEPF,
         max(EMPRECOVERYSTS) as recsts,
         DT.EMPNO
    FROM EMP_VALI VAL, EMP_SUPP DT
  WHERE VAL.EMPNO = DT.EMPNO AND VAL.EFLAG = DT.EFLAG AND
         VAL.EFLAG = 'Y' AND VAL.SFLAG = 'Y' AND
         VAL.EMPRECSTS = 'DEP' AND VAL.EMPNO IS NOT NULL and
         dt.RECDATE = val.RECDATE AND DT.SFLAG IS NOT NULL AND
         DT.SFLAG in ('F')
  GROUP BY DT.RECPAIDDATE, DT.EMPNO))
  GROUP BY RECDATE, EMPNO)
  /
  </code></pre>