我有以下查询:
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;
所以这里:LOCATION
是由用户在代码级别使用OCI8
. 例如:
require 'oci8'
cursor = conn.parse(query)
cursor.bind_param(':LOCATION', 'Chicago', String)
我只希望从 1 到 4 的选项之一成为最终查询的一部分。例如,如果选项 3 为真(:BEGIN
is NULL
& :END
is not NULL
),那么最终查询将是:
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;
用户提供:END
日期的地方
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)
并会导致:
SELECT *
FROM FOO
WHERE LOCATION = 'chicago'
AND MY_DATE <= TIMESTAMP '2001-01-22 12:01:00'
ORDER BY MY_DATE;
如何编写查询以允许此逻辑?
一个选项是使用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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句