CODEWITHSUNDEEP
sqloracle-databaseplsql
Ciarán Bruen
Ciarán Bruen

Reputation: 5341

Case expression in Oracle where clause

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:

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

What I now want to do is something like this:

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

Is this possible with a case expression in the where clause?

@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:

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)

Upvotes: 1

Views: 17088

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

I'm more of a SQL Server guy, but the following should do the trick (assuming Oracle's not equal to is <>, not !=):

 (CASE WHEN p_appr_status is null and APPR_STATUS<>101 THEN APPR_STATUS
  WHEN p_appr_status is not null THEN (p_appr_status)
  END)

Upvotes: 3

Related Questions