Search code examples
oracle-databaseplsqlnvl

Oracle nvl in where clause showing strange results?

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?

Solution

  • No it shouldn't.

    The SEC_CODE in the database is null, so the UPPER(SEC_CODE) is null and so it will fail on a LIKE match or pretty much any other comparison beyond IS NULL. Technically it is a UNKNOWN rather than a FALSE but is isn't enough to pass the test.