Seeing ORA-00905: missing keyword error due to CASE statement in WHERE clause-CodePudding

I'm seeing a ORA-00905: missing keyword at Error at Position: 181 and can't seem to figure out what's wrong with the SQL (Oracle PL/SQL).

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

My assumption is that in the event user input is:

:LOCATION = 'new york'
:BEGIN_D = NULL
:END_D = NULL

then query is deduced to:

SELECT *
FROM FOO
    WHERE LOCATION = 'new york'
      AND SAVED_DATE >= SAVED_DATE -- This line is ignored
      AND SAVED_DATE <= SYSDATE
 ORDER BY SAVED_DATE;

However I'm seeing the error mentioned at the beginning.

When the input is not NULL (Ex: :BEGIN_D = '2015-12-01 00:01:44') I do not see an error. If either or both :BEGIN_D and :END_D is NULL the error comes back.

CodePudding user response：

Bind variables are not substitution variables; they are not replaced by the text you input so you cannot use TIMESTAMP :BEGIN_D. You would just want to use :BEGIN_D and pass in a TIMESTAMP data type.

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;

You also do not need to use a CASE expression:

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;

If you are passing in a string value (and not a timestamp) then use 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;