I have the below query:
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND MY_DATE >= TIMESTAMP :BEGIN AND MY_DATE <= TIMESTAMP :END -- option 1 if :BEGIN & :END is not NULL
AND MY_DATE >= TIMESTAMP :BEGIN AND MY_DATE <= sysdate -- option 2 if :BEGIN is not NULL & :END is NULL
AND MY_DATE <= TIMESTAMP :END -- option 3 if :BEGIN is NULL & :END is not NULL
AND MY_DATE <= sysdate -- option 4 if both :BEGIN & :END is NULL
ORDER BY MY_DATE;
so here :LOCATION is supplied by the user on code level using OCI8. For example:
require 'oci8'
cursor = conn.parse(query)
cursor.bind_param(':LOCATION', 'Chicago', String)
I only want one of the options from 1 to 4 to be part of the final query. For example if option 3 is true (:BEGIN is NULL & :END is not NULL) then the final query will be:
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND MY_DATE <= TIMESTAMP :END -- option 3 if :BEGIN is not NULL & :END is NULL
ORDER BY MY_DATE;
Where user would supply an :END date
require 'oci8'
cursor = conn.parse(query)
cursor.bind_param(':LOCATION', 'Chicago', String)
cursor.bind_param(':START', NULL)
cursor.bind_param(':END', '2001-01-22 12:01:00', String)
and would result in:
SELECT *
FROM FOO
WHERE LOCATION = 'chicago'
AND MY_DATE <= TIMESTAMP '2001-01-22 12:01:00'
ORDER BY MY_DATE;
How do I write a query to allow this logic?
An option is to use CASE :
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND MY_DATE >= CASE WHEN :BEGIN IS NOT NULL THEN :BEGIN
ELSE MY_DATE
END
AND MY_DATE <= CASE WHEN :END IS NOT NULL THEN :END
ELSE SYSDATE
END
ORDER BY MY_DATE;