title: Case expression in Oracle where clause
body: <p>I have a standard search clause whereby I'm selecting records on certain filters such as description and status, the status values being 101 to 110. Status may be null, if so I return records of any status. However I now have a new status which has to be excluded from the returned records when there is no specific status, and only returned when specifically selected. So a search based on a specific status returns just that status, a search without a specific status returns all statuses except the new one. The original where clause is:</p>
  
  <pre><code>where Upper(cfs.CAE_SEC_ID) = Upper(NVL(p_cae_sec_id_n,cfs.CAE_SEC_ID)) 
  and Upper(SEC_CODE) like '%' || Upper(NVL(p_fm_sec_code_c,SEC_CODE)) || '%' 
  and APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
  order by appr_status DESC, cae_sec_id 
  </code></pre>
  
  <p>What I now want to do is something like this:</p>
  
  <pre><code>where Upper(cfs.CAE_SEC_ID) = Upper(NVL(p_cae_sec_id_n,cfs.CAE_SEC_ID)) 
  and Upper(SEC_CODE) like '%' || Upper(NVL(p_fm_sec_code_c,SEC_CODE)) || '%' 
  and APPR_STATUS =
    (CASE WHEN p_appr_status is null THEN --return all statuses except 110
    WHEN p_appr_status is not null THEN (p_appr_status)
    END)
  order by appr_status DESC, cae_sec_id 
  </code></pre>
  
  <p>Is this possible with a case expression in the where clause?</p>
  
  <hr>
  
  <p>@Damien provided the answer so thanks to him for that. There is another scenario I need to cater for - the same proc returns individual as well as multiple records. If someone searches for an individual record (p_cae_sec_id_n is not null) that has a status of ignore then that was being excluded from above, so I've added it in below:</p>
  
  <pre><code>and APPR_STATUS = 
    (CASE WHEN p_appr_status is null and APPR_STATUS != 110 THEN APPR_STATUS
    WHEN (p_appr_status is null and p_cae_sec_id_n is not null) THEN APPR_STATUS
    WHEN p_appr_status is not null THEN (p_appr_status)
    END)
  </code></pre>