WHERE子句中使用CASE表达式遇ORA-00905错误的技术求助

首先咱们直接点出问题根源：Oracle的CASE表达式不能直接返回布尔值。你原来的写法里，每个CASE分支都写了类似a.trx_date >= p_trx_date_low的条件判断——这是布尔结果，但Oracle的CASE只能返回标量值（比如日期、数字、字符串），这种写法不符合语法规则，自然就抛出了ORA-00905错误。

最优解决方案：用逻辑运算符直接组合条件 #

其实你完全不需要依赖CASE来实现这个逻辑，直接用AND/OR把各个场景的条件组合起来就行，和你原来动态拼接SQL的逻辑完全对应，而且更简洁，Oracle优化器也更容易生成高效的执行计划：

AND (
    -- 两个日期参数都为空时，所有行都满足条件
    (p_trx_date_low IS NULL AND p_trx_date_high IS NULL)
    -- 只有起始日期参数，判断交易日期 >= 起始值
    OR (p_trx_date_low IS NOT NULL AND p_trx_date_high IS NULL AND a.trx_date >= p_trx_date_low)
    -- 只有结束日期参数，判断交易日期 <= 结束值
    OR (p_trx_date_low IS NULL AND p_trx_date_high IS NOT NULL AND a.trx_date <= p_trx_date_high)
    -- 两个日期参数都存在，用BETWEEN判断范围
    OR (p_trx_date_low IS NOT NULL AND p_trx_date_high IS NOT NULL AND a.trx_date BETWEEN p_trx_date_low AND p_trx_date_high)
)

可选（不推荐）：调整CASE表达式的写法 #

如果你一定要用CASE来实现，那得改成让CASE返回一个可用于判断的标识值（比如'Y'/'N'），再通过判断标识值来筛选数据，写法如下：

AND CASE
    WHEN p_trx_date_low IS NULL AND p_trx_date_high IS NULL THEN 'Y'
    WHEN p_trx_date_low IS NOT NULL AND p_trx_date_high IS NULL THEN 
        CASE WHEN a.trx_date >= p_trx_date_low THEN 'Y' ELSE 'N' END
    WHEN p_trx_date_low IS NULL AND p_trx_date_high IS NOT NULL THEN 
        CASE WHEN a.trx_date <= p_trx_date_high THEN 'Y' ELSE 'N' END
    WHEN p_trx_date_low IS NOT NULL AND p_trx_date_high IS NOT NULL THEN 
        CASE WHEN a.trx_date BETWEEN p_trx_date_low AND p_trx_date_high THEN 'Y' ELSE 'N' END
END = 'Y'

不过这种写法嵌套了CASE，可读性和性能都不如直接用逻辑组合的方案，所以更推荐第一种解决方式。

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