oracle - ORA-01722 : invalid number when creating materialized view

Question

我正在创建一个 View ，并将同一个 View 转换为同一系统中的物化 View 。但是在另一个系统中做同样的事情我得到了错误 ORA-01722: invalid number创建物化 View 时。为什么？

create materialized view MV_EMP_VALI 
 refresh complete with rowid start with SYSDATE+1/24 AS 
 (select * from V_CHA1);

看法:-

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 >'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)
/

Answer 1

从声明中很难判断，但如果非要我猜的话，我把钱花在了表达上:

RECDATE >'01-Apr-2011'

假设列 RECDATE实际上是类型 DATE .因此 Oracle 尝试转换字符值 '01-Apr-2011'到 DATE 也是如此。由于您没有为此指定格式掩码，因此使用默认 NLS 设置。如果他们为月份定义了一个数字，那么上述值将无法转换。

您应该 从不 依赖隐式数据类型转换。尤其不是日期。改用 ANSI 文字:

RECDATE > DATE '2011-04-01'

或使用带有格式掩码的 to_date() 函数:

RECDATE > to_date('01-Apr-2011', 'dd-mon-yyyy')

请注意，对于 NLS_LANG 的某些设置，这仍然可能会失败。在法语中，您需要指定“Avr”而不是“Apr”。因此，除非您绝对确定您可以一直控制所有 NLS_XXX 设置，否则我强烈建议您改用月份数字。如果您更喜欢使用 to_date() 而不是 ANSI 文字，您可以使用:

RECDATE > to_date('01-04-2011', 'dd-mm-yyyy')

编辑

如果它不是日期列，您需要检查任何其他列以进行隐式数据转换。

这些表达式:

sum(round(NVL(trim(DT.EMO), 0)))
round(NVL(trim(EMOLUMENTS), 0))
round(NVL(trim(DT.EMO), 0))
round(NVL(trim(DT.EPF), 0))

看起来很可疑。如果其中的列是实数，则 trim()无效且无用。如果这些不是数字，它们可能会导致该错误，具体取决于列的内容。

这个表达式 to_number(SUM(CPFEMO))也没有用，因为 sum() 已经返回一个数字，没有理由在 number() 上调用 to_number。虽然我怀疑它会引发你的错误，但你仍然应该避免它，因为它没有任何意义。