出现错误-ORA-01858:在期望数字的位置找到了非数字字符 [英] Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected

问题描述

我在下面的sql中收到错误:

I am getting the error in the below sql:

ORA-01858:在需要数字的地方找到了非数字字符

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

SELECT   c.contract_num,
         CASE
            WHEN   (  MAX (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                    - MIN (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                 / COUNT (c.event_occurrence) < 32
            THEN
              'Monthly'
            WHEN       (  MAX (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                        - MIN (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                     / COUNT (c.event_occurrence) >= 32
                 AND   (  MAX (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                        - MIN (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                     / COUNT (c.event_occurrence) < 91
            THEN
              'Quarterley'
            ELSE
              'Yearly'
         END
FROM     ps_ca_bp_events c
GROUP BY c.contract_num;

推荐答案

您收到的错误是因为您在已经是日期的列上执行了TO_DATE，并且您使用的是不同的格式掩码设置为nls_date_format参数[1]，或者因为event_occurrence列包含的数据不是数字.

The error you are getting is either because you are doing TO_DATE on a column that's already a date, and you're using a format mask that is different to your nls_date_format parameter[1] or because the event_occurrence column contains data that isn't a number.

您需要a)更正您的查询，以使其不在日期列上使用TO_DATE，并且b)如果event_occurrence应该只是数字，则更正您的数据.

You need to a) correct your query so that it's not using TO_DATE on the date column, and b) correct your data, if event_occurrence is supposed to be just numbers.

并修复该列的数据类型，以确保您只能存储数字.

And fix the datatype of that column to make sure you can only store numbers.

[1] Oracle在执行以下操作时会执行以下操作:TO_DATE(date_column, non_default_format_mask)是: TO_DATE(TO_CHAR(date_column, nls_date_format), non_default_format_mask)

[1] What Oracle does when you do: TO_DATE(date_column, non_default_format_mask) is: TO_DATE(TO_CHAR(date_column, nls_date_format), non_default_format_mask)

通常，默认的nls_date_format参数设置为dd-MON-yy，因此在您的查询中，可能发生的情况是将日期列转换为dd-MON-yy格式的字符串，然后使用MMDD格式将其恢复为日期.该字符串不是这种格式，因此会出现错误.

Generally, the default nls_date_format parameter is set to dd-MON-yy, so in your query, what is likely to be happening is your date column is converted to a string in the format dd-MON-yy, and you're then turning it back to a date using the format MMDD. The string is not in this format, so you get an error.

这篇关于出现错误-ORA-01858:在期望数字的位置找到了非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！