I have a web form that allows users to search on and edit records from an Oracle table based on parameters passed in to a proc. Here's my data:
CAE_SEC_ID SEC_CODE APPR_STATUS
1 ABC1 100
2 ABC2 100
3 ABC3 101
4 (null) 101
5 (null) 102
6 ABC4 103
And here's the where clause:
select foo
from bar
where CAE_SEC_ID = NVL(p_cae_sec_id,CAE_SEC_ID)
and Upper(SEC_CODE) like '%' || Upper(NVL(p_sec_code,SEC_CODE)) || '%'
and APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
Using nvl on the parameters should return only the matched records if any of the parameters have values, and all records if none of the parameters have values. All pretty standard or so I thought. However when I do a search without any parameter values the query isn't returning records with a null SEC_CODE i.e. only records 1, 2, 3, and 6 are being returned. Shouldn't the where clause above include records with null SEC_CODE values?
The problem is that the SEC_CODE
value in the table is NULL. That means that UPPER(sec_code)
is NULL and your second predicate simplifies to
and NULL LIKE '%%'
Just like NULL is not equal to anything and not unequal to anything, it is not like anything. Most likely, you want something like
and (Upper(SEC_CODE) like '%' || Upper(NVL(p_sec_code,SEC_CODE)) || '%' or
(sec_code is null and p_sec_code is null))
That will return every row if P_SEC_CODE
is NULL but still apply the filter if P_SEC_CODE
is non-NULL.