预言机中大小写子句内的关系运算符

Question

以下简单代码出现"缺少关键字"错误：如果 i_check = 'Y'，我如何得到结果 3、4 和 5，如果 i_check = 'N'，则只有 4 和 5

WITH t(val) AS
     ( SELECT 1 FROM dual
     UNION ALL
     SELECT 2 FROM dual
     UNION ALL
     SELECT 3 FROM dual
     UNION ALL
     SELECT 4 FROM dual
     UNION ALL
     SELECT 5 FROM dual
     )
SELECT *
FROM t
WHERE CASE WHEN i_check  = 'Y' THEN VAL >= 3
           WHEN i_check  = 'N' THEN VAL  > 3
      END;

使用清晰的数据进行编辑：

 WITH t(val) AS
     ( SELECT trunc(sysdate) FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '10' minute FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '1' hour FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '2' hour + interval '1' second FROM dual
     UNION ALL
     SELECT trunc(sysdate) + 1 FROM dual
     )
SELECT *
FROM t
19/02/2014 12:00:00 AM
19/02/2014 12:10:00 AM
19/02/2014 01:00:00 AM
19/02/2014 02:00:01 AM
20/02/2014 12:00:00 AM

输入是i_check和i_date

如果i_check = 'Y' AND i_date = 19/02/2014 01：00：00 AM'' 则

19/02/2014 01:00:00 AM, 19/02/2014 02:00:01 AM, 20/02/2014 12:00:00 AM 

如果i_check = 'N' AND i_date = 19/02/2014 01：00：00 AM'' 则

19/02/2014 02:00:01 AM, 20/02/2014 12:00:00 AM 

提前感谢您的任何帮助：）

Answer 1

您不能将boolean condition返回到 where 子句，而是像这样使用大小写

 WITH t(val) AS
     ( SELECT 1 FROM dual
     UNION ALL
     SELECT 2 FROM dual
     UNION ALL
     SELECT 3 FROM dual
     UNION ALL
     SELECT 4 FROM dual
     UNION ALL
     SELECT 5 FROM dual
     )
SELECT *
FROM t
WHERE val > (CASE WHEN i_check  = 'Y' THEN  2
                  WHEN i_check  = 'N' THEN  3
              END);

Answer 2

CASE WHEN 必须返回一个值，而不是一个语句。为什么不使用简单的逻辑 AND/OR 语句？:)例如，可以像这样重写第一个查询：

WITH t(val) AS
     ( SELECT 1 FROM dual
     UNION ALL
     SELECT 2 FROM dual
     UNION ALL
     SELECT 3 FROM dual
     UNION ALL
     SELECT 4 FROM dual
     UNION ALL
     SELECT 5 FROM dual
     )
SELECT *
FROM t
WHERE (i_check  = 'Y' AND VAL >= 3)
      OR (i_check  = 'N' AND VAL > 3);

---

至于第二个查询，应该是这样的：

WITH t(val) AS
     ( SELECT trunc(sysdate) FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '10' minute FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '1' hour FROM dual
     UNION ALL
     SELECT trunc(sysdate) + interval '2' hour + interval '1' second FROM dual
     UNION ALL
     SELECT trunc(sysdate) + 1 FROM dual
     )
SELECT *
FROM t
WHERE (:i_check = 'Y' AND VAL >= :i_date)
      OR (:i_check = 'N' AND VAL > :i_date)