甲骨文.使用case语句时缺少关键字.错误00905 [英] Oracle. Missing keyword when using case statement. Error 00905

问题描述

自从我引入CASE语句以来，以下语句一直出现错误"ORA-00905:缺少关键字"，但是我无法弄清丢失了什么.

I keep getting an error 'ORA-00905: missing keyword' with the following statement, ever since I introduced the CASE statement, but I can't figure out what is missing.

SELECT
    CYCLE_S_FACT_MAIN.STARTTIME,
    CYCLE_S_FACT_MAIN.ENDTIME
FROM
  CYCLE_S_FACT_MAIN
WHERE
  (
   CYCLE_S_FACT_MAIN.ENDTIME  > 
    (SELECT SYSDATE,
        CASE SYSDATE
            WHEN TO_CHAR(SYSDATE, 'HH') < 6 THEN CONCAT(TO_CHAR(SYSDATE, 'DD-MM-YYYY'), ' 06:00:00')
            ELSE CONCAT(TO_CHAR(SYSDATE - INTERVAL '1' DAY, 'DD-MM-YYYY'), ' 06:00:00')
        END AS SYSDATE
    FROM DUAL
    )
   AND
   CYCLE_S_FACT_MAIN.ENDTIME  <= SYSDATE
  )

推荐答案

Damien_The_Unbeliever关于混合大小写样式是正确的，但是您也根本不需要子查询，而您拥有的子查询将返回两列-您可以不能与单个值进行比较.您可以这样做:

Damien_The_Unbeliever is right about mixing case styles, but you also don't need the subquery at all, and the one you have is getting two columns back - which you can't compare with a single value. You can just do this:

WHERE
   CYCLE_S_FACT_MAIN.ENDTIME > CASE
        WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < 6
          THEN TRUNC(SYSDATE) + INTERVAL '6' HOUR
        ELSE TRUNC(SYSDATE) - INTERVAL '1' DAY + INTERVAL '6' HOUR END
   AND CYCLE_S_FACT_MAIN.ENDTIME  <= SYSDATE

这使比较保留在两个日期之间，而不是依赖于隐式转换.我也用过HH24;使用HH可以将午夜至下午6点之间的时间与午夜至凌晨6点之间的时间相同，我敢肯定您不打算这样做.

This leaves the comparison as between two dates, rather than relying on implcit conversions. I've also used HH24; using HH would treat times between midday and 6pm the same as those between midnight and 6am, which I'm prety sure you didn't intend.

这篇关于甲骨文.使用case语句时缺少关键字.错误00905的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！