Oracle SQL查询WHERE子句中CASE语句报Missing Keyword错误，寻求按月/年筛选可续订产品的正确写法

问题背景 #

你有一批按月（renew='M'）或按年（renew='Y'）续订的产品，需要根据指定的P_date和续订方式筛选符合条件的产品。现有产品表数据：

需求是：当P_date为06-01-2022且选'M'时，返回P_no=1001；选'Y'时返回P_no=1002。但你写的SQL执行时提示"Missing Keyword"错误：

select * from products where P_no in('1001','1002') and 
CASE renew 
WHEN renew = 'M' and round(months_between(renewal_date,start_date)) = 1 then TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN start_date AND end_date 
WHEN renew='Y' and round(months_between(renewal_date,start_date)) = 12 then TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN start_date AND end_date 
end ;

错误原因 #

你的CASE表达式写法不符合Oracle的语法规则：

1. CASE语法混用：你同时混用了两种CASE写法——CASE renew WHEN ...的简单CASE格式，和WHEN 条件 THEN ...的搜索CASE格式。简单CASE是将renew与WHEN后的值直接比较，而你在WHEN后写了复合条件判断，这会触发语法错误。
2. 不支持返回布尔值：Oracle的CASE表达式不能直接返回布尔结果（比如BETWEEN ...这类条件），它必须返回具体的数据类型（如数字、字符串），无法直接用布尔值作为WHERE子句的判断依据。

正确的写法 #

我们可以直接用逻辑条件组合来替代CASE，写法更清晰且符合语法：

SELECT * 
FROM products 
WHERE P_no IN ('1001','1002')
  AND (
    -- 按月续订的匹配条件
    (renew = 'M' 
     AND ROUND(MONTHS_BETWEEN(renewal_date, start_date)) = 1 
     AND TO_DATE(P_date, 'DD-MM-YYYY') BETWEEN start_date AND end_date)
    OR
    -- 按年续订的匹配条件
    (renew = 'Y' 
     AND ROUND(MONTHS_BETWEEN(renewal_date, start_date)) = 12 
     AND TO_DATE(P_date, 'DD-MM-YYYY') BETWEEN start_date AND end_date)
  );

注意：不要把TO_DATE转换后的日期再用TO_CHAR转成字符串，直接用日期类型比较更准确，能避免字符串格式不匹配的问题。

如果确实想使用CASE表达式（虽然这里没必要），可以让CASE返回数字，再判断结果是否为1：

SELECT * 
FROM products 
WHERE P_no IN ('1001','1002')
  AND CASE 
        WHEN renew = 'M' AND ROUND(MONTHS_BETWEEN(renewal_date, start_date)) = 1 
             AND TO_DATE(P_date, 'DD-MM-YYYY') BETWEEN start_date AND end_date THEN 1
        WHEN renew = 'Y' AND ROUND(MONTHS_BETWEEN(renewal_date, start_date)) = 12 
             AND TO_DATE(P_date, 'DD-MM-YYYY') BETWEEN start_date AND end_date THEN 1
        ELSE 0
      END = 1;

测试验证 #

当P_date='06-01-2022'且筛选renew='M'时，会匹配到P_no=1001；筛选renew='Y'时，匹配到P_no=1002，完全符合你的需求。

内容的提问来源于stack exchange，提问作者lakshganga