title: Trying to use IF/CASE Statement within a Where Clause using BETWEEN in Oracle
body: <p>I am trying to do the following CASE statement within a WHERE clause and it's not working. I have searched everywhere and can't find a solution. The error report says:</p>
  
  <pre><code>PL/SQL: ORA-00905: missing keyword.
  </code></pre>
  
  <p>Which keyword is missing?</p>
  
  <pre><code>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)
        ) ;
  </code></pre>
  
  <p>I tried this method but it's not giving me the results I desire. Can someone explain why? Oracle seems only be using v_xs_scope(j).v_enriched_time regardless of whether Enriched_Date is in the date_stamp field or not. </p>
  
  <pre><code>    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) 
  </code></pre>