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

Question

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

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.

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

Answer 1

我不是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

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

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

您应该替换为

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

在你的表情中无处不在。