我在访问日期查询时遇到错误

I am getting this error with this date query :

我在这个日期查询中收到此错误:

SELECT Y.ITEM_ID
FROM (
    SELECT DISTINCT DOM1.ITEM_ID
        ,COUNT(1)
    FROM (
        SELECT ITEM_ID
            ,COUNT(1)
            ,MAX(ATTRIBUTE_1) ATTRIBUTE_1
        FROM (
            SELECT UNIQUE domain_index.item_id
                ,CASE 
                    WHEN domain_index.ATTRIBUTE_FIELD_ID = 382767
                        THEN DECODE(ATTRIBUTE_VALUE, NULL, '1A2B3C4D5E6F7G8H9I0J0K1L2M3N4O5P6Q7R8S9T0U5V4W3X2Y1Z', ATTRIBUTE_VALUE)
                    END AS ATTRIBUTE_1
            FROM T_DOMAIN_INDEX domain_index
            WHERE DOMAIN_ID = 64279
                AND (
                    ATTRIBUTE_FIELD_ID = 382767
                    AND (to_date('01 ' || nvl(to_char(to_date(replace(TRIM(ATTRIBUTE_VALUE), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'), 'dd/mm/yyyy') <> to_date('01 Mar 2017', 'dd/mm/yyyy'))
                    )
            )
        GROUP BY item_id
        ) DOM1
    WHERE (to_date('01 ' || nvl(to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'), 'dd/mm/yyyy') <> to_date('01 Mar 2017', 'dd/mm/yyyy'))
        AND (to_date('01 ' || nvl(to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'), 'dd/mm/yyyy') <> to_date('01 01 0001', 'dd/mm/yyyy'))
    GROUP BY DOM1.ITEM_ID
    ) Y
    ,T_DOMAIN_ITEM A
WHERE Y.ITEM_ID = A.ITEM_ID
    AND NVL(A.PARENT_ITEM_ID, 0) = 0
    AND A.IS_ARCHIVED = 0
ORDER BY DT_UPDATED DESC
    ,Y.ITEM_ID

Error : ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.

错误:ORA-01861:文字与格式字符串01861不匹配.00000 - “文字与格式字符串不匹配”*原因:输入中的文字长度必须与格式字符串中的文字长度相同(前导空格除外) 。如果已打开“FX”修饰符,则文字必须完全匹配,没有额外的空格。 *操作:更正格式字符串以匹配文字。

The attribute value can be a date in ISO format or null or blank. This error is received when one of the attribute value is blank.

属性值可以是ISO格式的日期,也可以为null或空白。当其中一个属性值为空时,会收到此错误。

2 个解决方案

#1

I'm not Oracle expect but your code

我不是Oracle的期望,而是你的代码

SELECT Y.ITEM_ID
FROM (SELECT DISTINCT
        DOM1.ITEM_ID,
        COUNT(1)
      FROM (SELECT
              ITEM_ID,
              COUNT(1),
              MAX(ATTRIBUTE_1) ATTRIBUTE_1
            FROM (SELECT UNIQUE
                    domain_index.item_id,
                    CASE WHEN domain_index.ATTRIBUTE_FIELD_ID = 382767
                      THEN DECODE(ATTRIBUTE_VALUE, NULL, '1A2B3C4D5E6F7G8H9I0J0K1L2M3N4O5P6Q7R8S9T0U5V4W3X2Y1Z',
                                  ATTRIBUTE_VALUE) END AS ATTRIBUTE_1
                  FROM T_DOMAIN_INDEX domain_index
                  WHERE DOMAIN_ID = 64279 AND (ATTRIBUTE_FIELD_ID = 382767 AND (to_date('01 ' || nvl(
                      to_char(to_date(replace(TRIM(ATTRIBUTE_VALUE), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'),
                      '01 0001'), 'dd/mm/yyyy') <> to_date('01 Mar 2017', 'dd/mm/yyyy'))))
            GROUP BY item_id) DOM1
      WHERE (to_date('01 ' || nvl(
          to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'),
                     'dd/mm/yyyy') <> to_date('01 Mar 2017', 'dd/mm/yyyy')) AND (to_date('01 ' || nvl(
          to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'),
                                                                                         'dd/mm/yyyy') <>
                                                                                 to_date('01 01 0001', 'dd/mm/yyyy'))
      GROUP BY DOM1.ITEM_ID) Y, T_DOMAIN_ITEM A
WHERE Y.ITEM_ID = A.ITEM_ID AND NVL(A.PARENT_ITEM_ID, 0) = 0 AND A.IS_ARCHIVED = 0
ORDER BY DT_UPDATED DESC, Y.ITEM_ID

has to_date function ans it has different date value and format. for example

具有to_date函数,它具有不同的日期值和格式。例如

to_date('01 01 0001', 'dd/mm/yyyy')

You should replace to

你应该替换为

to_date('01/01/0001', 'dd/mm/yyyy')

Everywhere in your expression.

你表达的每个地方。

#2

The problem stems from this:

问题源于此:

DECODE(ATTRIBUTE_VALUE, NULL,
 '1A2B3C4D5E6F7G8H9I0J0K1L2M3N4O5P6Q7R8S9T0U5V4W3X2Y1Z', ATTRIBUTE_VALUE)

When your attribute value is null, you're replacing it with that fixed string, aliased as ATTRIBUTE_1. That's in the inline view aliased as DOM1.

当您的属性值为null时,您将其替换为固定字符串,别名为ATTRIBUTE_1。这是在内联视图中别名为DOM1。

In the next level out you're filtering on

在下一个级别,你正在过滤

WHERE (to_date('01 ' || nvl(to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'), 'dd/mm/yyyy') ...

If you put the decoded value into that you are actually doing, or just this part:

如果您将解码后的值放入您实际执行的位置,或者仅将此部分放入:

to_date('01 ' || nvl(to_char(to_date(replace(TRIM(DOM1.ATTRIBUTE_1), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS')

you're really doing:

你真的在做:

select to_date(replace(TRIM('1A2B3C4D5E6F7G8H9I0J0K1L2M3N4O5P6Q7R8S9T0U5V4W3X2Y1Z'), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS') from dual;

and that clearly isn't a valid ISO (or any other) date format; hence the ORA-01861 error.

这显然不是有效的ISO(或任何其他)日期格式;因此ORA-01861错误。

Without knowing why you're using that fixed string in place of a null value, it isn't clear if you should just not be doing that decode, or should be treating that value differently - as an exception in the where clause perhaps. It seems pointless though as it isn't used for anything except the date conversion.

如果不知道为什么使用该固定字符串代替空值,则不清楚是否应该不进行该解码,或者应该以不同方式处理该值 - 作为where子句中的异常。这似乎毫无意义,因为除了日期转换之外它没有被用于任何东西。

Incidentally, you seem to be doing a lot of conversions to and from dates, just to get the first day of the month. Instead of doing something like:

顺便说一下,你似乎要在日期之间进行大量的转换,只是为了获得本月的第一天。而不是做这样的事情:

select to_date('01 ' || nvl(to_char(to_date(replace(TRIM('2017-03-02T17:04:30'), 'T', ' '),
  'YYYY-MM-DD HH24:MI:SS'), 'Mon YYYY'), '01 0001'), 'dd/mm/yyyy')
from dual;

you could do:

你可以这样做:

select nvl(trunc(to_date('2017-03-02T17:04:30', 'YYYY-MM-DD"T"HH24:MI:SS'), 'MM'), 
  date '0001-01-01') from dual;

You should also be using date format masks consistently, as @DmitryGorkovets pointed out, but you could also use ANSI date literals for the fixed dates, e.g. instead of doing to_date('01 Mar 2017', 'dd/mm/yyyy') you could do date '2017-03-01'.

您也应该像@DmitryGorkovets所指出的那样,始终如一地使用日期格式掩码,但您也可以使用ANSI日期文字来表示固定日期,例如:而不是做to_date('2017年3月1日','dd / mm / yyyy')你可以约会'2017-03-01'。

#1