看到ORA-01858:在需要数字的地方找到了非数字字符 [英] Seeing ORA-01858: a non-numeric character was found where a numeric was expected

问题描述

以下是我对Oracle SQL Developer的查询:

The following is my query for Oracle SQL Developer:

INSERT INTO ENROLLMENTS (DATE,PARTNER_NAME,ENROLLMENTS)
    SELECT TO_CHAR(TS, 'DD-MON-YYYY HH AM') AS DATE, mrch_bnft_cd, COUNT(*)
    FROM ENROLLMENTS 
    WHERE TS > trunc(sysdate-1/24, 'HH') + 5/24 
    AND TS < trunc(sysdate, 'HH') + 5/24
    GROUP BY TO_CHAR(TS, 'DD-MON-YYYY HH AM'), mrch
    ORDER BY TO_CHAR(TS, 'DD-MON-YYYY HH AM'), mrch_bnft

我得到一个错误

ORA-01858:在预期出现数字错误的地方发现了一个非数字字符

ORA-01858: a non-numeric character was found where a numeric was expected error

但是错误没有告诉我在哪里.有任何想法吗?

but the error is not telling me where. Any ideas?

DATE = TIMESTAMP(6)
TS = TIMESTAMP(6)  
PARTNER = VARCHAR2(35 BYTE)  
ENROLLMENTS = NUMBER  
MRCH_BNFT= VARCHAR2(35 BYTE)

推荐答案

基本上，您正在尝试在时间戳字段中存储代表日期的字符串. 如果您替换:

Basically you are trying to store a string which represents a date in a timestamp field. If you replace:

TO_CHAR(TS, 'DD-MON-YYYY HH AM')

作者

TRUNC(TS, 'hh24')

它应该在所有4个地方工作.

at all 4 places it should work.

INSERT INTO ENROLLMENTS (DATE,PARTNER_NAME,ENROLLMENTS)
    SELECT   TRUNC(TS, 'hh24') AS DATE, mrch_bnft_cd, COUNT(*)
    FROM     ENROLLMENTS 
    WHERE    TS > trunc(sysdate-1/24, 'HH') + 5/24 
    AND      TS < trunc(sysdate, 'HH') + 5/24
    GROUP BY TRUNC(TS, 'hh24'), mrch
    ORDER BY TRUNC(TS, 'hh24'), mrch_bnft

这篇关于看到ORA-01858:在需要数字的地方找到了非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！