主页 / coding / 问题 / 77268913
Accepted
Nad
Asked: 2023-10-11 04:57:00 +0800 CST

ORA-01722: 在 Oracle 中运行存储过程时数字无效

  • 772

我有一个查询，我正在执行它并得到以下错误：

ORA-01722: 无效数字

这是查询。

SELECT TO_CHAR (tfc.linkid) spanid,
            TO_CHAR (tfc.mz_code) AS maint_zone_code,
            TO_CHAR (tfc.mz_name) AS maint_zone_name,
            SUM (tfc.mho_handover_cert) AS ne_length,
            TRUNC (tfc.created_date) AS offered_date     
       FROM app_lco.tbl_fip_checklist tfc
      WHERE     LENGTH (TRIM (tfc.linkid)) > 8
            AND LENGTH (TRIM (tfc.linkid)) < 21
            AND tfc.status = 'APPROVED'
   GROUP BY TO_CHAR (tfc.linkid),
            TO_CHAR (tfc.mz_code),
            TO_CHAR (tfc.mz_name),
            TRUNC(tfc.created_date);

表说明如下

Name                 Null Type           
-------------------- ---- --------------     
MHO_HANDOVER_CERT         VARCHAR2(20)   
STRAIGHT_LINE_DIAG        VARCHAR2(5)    
OFFERID                   VARCHAR2(10)   
CREATED_DATE              DATE           
SUBMITTED_BY              VARCHAR2(50)   
LINKID                    VARCHAR2(50)   
SPANID                    VARCHAR2(50)   
STATUS                    VARCHAR2(15)   
FILE_NAME                 VARCHAR2(1000) 
SAP_ID                    VARCHAR2(30)   
STATE_NAME                VARCHAR2(50)   
MZ_NAME                   VARCHAR2(70)   
MZ_CODE                   VARCHAR2(30)
oracle

1 个回答

  1. Best Answer

    不要将数值存储在字符串中；如果你想存储数值，那么使用NUMBER.

    首先修复您的数据，然后您的查询将起作用。

    如果您无法修复数据（为什么？您应该修复它！），那么从 Oracle 12 开始，您可以使用TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)：

    SELECT linkid  AS spanid,
       mz_code AS maint_zone_code,
       mz_name AS maint_zone_name,
       SUM(TO_NUMBER(mho_handover_cert DEFAULT NULL ON CONVERSION ERROR))
         AS ne_length,
       TRUNC(created_date) AS offered_date     
FROM   app_lco.tbl_fip_checklist
WHERE  LENGTH(TRIM(linkid)) > 8
AND    LENGTH(TRIM(linkid)) < 21
AND    status = 'APPROVED'
GROUP BY
       linkid,
       mz_code,
       mz_name,
       TRUNC(created_date);

    注意：您不需要TO_CHAR在已经存在的列上使用VARCHAR2。

    其中，对于样本数据：

    CREATE TABLE app_lco.tbl_fip_checklist (linkid, mz_code, mz_name, mho_handover_cert, created_date, status) AS
  SELECT 'AAAAAAAAA', 'B', 'C', 'abc', DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
  SELECT 'AAAAAAAAA', 'B', 'C', '1'  , DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
  SELECT 'AAAAAAAAA', 'B', 'C', '2'  , DATE '1970-01-01', 'APPROVED' FROM DUAL UNION ALL
  SELECT 'AAAAAAAAA', 'B', 'C', '3e1', DATE '1970-01-01', 'APPROVED' FROM DUAL;

    输出：

    西班牙人 MAINT_ZONE_CODE MAINT_ZONE_NAME NE_LENGTH OFFERED_DATE
    啊啊啊啊啊 乙 C 33 1970-01-01 00:00:00

    小提琴

    • 1

相关问题