oracle case关键字,试图在Where子句中使用IF/CASE语句在Oracle中使用BETWEEN

我试图在WHERE子句中执行以下CASE语句，但它不起作用。我到处搜索并找不到解决方案。错误报告说：试图在Where子句中使用IF/CASE语句在Oracle中使用BETWEEN

PL/SQL: ORA-00905: missing keyword.

哪个关键字缺失？

SELECT COUNT(*)

INTO v_sql_count

FROM xaction_level_info b

WHERE v_measure_map(i).v_upload_code = b.UPLOAD_CODE

AND v_xs_scope(j).v_scope_key = b.SCOPE

AND

CASE WHEN

v_measure_map(i).v_date_stamp='Benchmark_Date' THEN

v_xs_scope(j).v_enriched_date BETWEEN NVL(b.effective_start_time, 00000000000000) AND NVL(b.effective_end_time, 99999999999999)

ELSE

v_xs_scope(j).v_enriched_time BETWEEN NVL(b.effective_start_time, 00000000000000) AND NVL(b.effective_end_time, 99999999999999)

END

AND b.level_order =

(SELECT MIN(level_order)

FROM xaction_level_info

WHERE SCOPE = b.SCOPE

AND UPLOAD_CODE = b.UPLOAD_CODE

AND NVL(effective_start_time, 00000000000000) = NVL(b.effective_start_time, 00000000000000)

AND NVL(effective_end_time, 99999999999999) = NVL(b.effective_end_time, 99999999999999)

) ;

我试过这种方法，但它没有给我我想要的结果。有人能解释为什么吗？不管Enriched_Date是否在date_stamp字段中，Oracle似乎只使用v_xs_scope(j).v_enriched_time。

CASE WHEN

v_measure_map(i).v_date_stamp='Enriched_Date' THEN

v_xs_scope(j).v_enriched_date

when

v_measure_map(i).v_date_stamp='Benchmark_Date' THEN

v_xs_scope(j).v_benchmark_date

ELSE

v_xs_scope(j).v_enriched_time

END

BETWEEN NVL(b.effective_start_time, 00000000000000) AND NVL(b.effective_end_time, 99999999999999)

2013-05-09

Holler