请参阅ORA-00905:WHERE子句中的CASE语句导致缺少关键字错误

绑定变量不是替换变量;它们不会被输入的文本替换,因此无法使用 TIMESTAMP :BEGIN_D .你只想用 :BEGIN_D 然后通过一个 TIMESTAMP 数据类型。

SELECT *
FROM   FOO
WHERE  LOCATION = :LOCATION
AND    SAVED_DATE >= CASE WHEN :BEGIN_D IS NULL THEN SAVED_DATE ELSE :BEGIN_D END
AND    SAVED_DATE <= CASE WHEN :END_D IS NULL THEN SYSDATE ELSE :END_D END
ORDER BY SAVED_DATE;

您也不需要使用 CASE 表达方式:

SELECT *
FROM   FOO
WHERE  LOCATION = :LOCATION
AND    (:BEGIN_D IS NULL OR SAVED_DATE >= :BEGIN_D)
AND    ((:END_D IS NULL AND SAVED_DATE <= SYSDATE) OR SAVED_DATE <= :END_D)
ORDER BY SAVED_DATE;

如果传递的是字符串值(而不是时间戳),则使用 TO_DATE :

SELECT *
FROM   FOO
WHERE  LOCATION = :LOCATION
AND    (:BEGIN_D IS NULL OR SAVED_DATE >= TO_DATE(:BEGIN_D, 'YYYY-MM-DD HH24:MI:SS'))
AND    ((:END_D IS NULL AND SAVED_DATE <= SYSDATE) OR SAVED_DATE <= TO_DATE(:END_D, 'YYYY-MM-DD HH24:MI:SS'))
ORDER BY SAVED_DATE;