3

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?

Doug Porter
  • 7,721
  • 4
  • 40
  • 55
Ciarán Bruen
  • 5,221
  • 13
  • 59
  • 69

4 Answers4

7

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
4

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
3

The expression NULL = NULL evaluates to NULL, which is not true and so these rows won't be returned. If I understand the requirement correctly, you only want to filter if a parameter is different from null, so I would rewrite the query like this to make the filter more explicit:

select foo from bar 
where (p_cae_sec_id is null or CAE_SEC_ID = p_cae_sec_id)
and (p_sec_code is null or Upper(SEC_CODE) like '%' || Upper(p_sec_code) || '%')
and (p_appr_status is null or APPR_STATUS = p_appr_status)

Setting the p_sec_code parameter to null will now return all rows, ignoring the value in the SEC_CODE column.

Jörn Horstmann
  • 33,639
  • 11
  • 75
  • 118
0

We can also write the Jorn's query like

select foo from bar 
where (CASE WHEN p_cae_sec_id is null THEN 'Y'
            WHEN CAE_SEC_ID = p_cae_sec_id THEN 'Y'
                    ELSE 'N'
                    END)='Y'
and   (CASE WHEN p_sec_code is null THEN 'Y'
            WHEN Upper(SEC_CODE) like '%' || Upper(p_sec_code) || '%' THEN 'Y'
                    ELSE 'N'
                    END)='Y'
and (CASE WHEN p_appr_status is null THEN 'Y'
                    WHEN APPR_STATUS = p_appr_status THEN 'Y'
                ELSE 'N'
                END)='Y'

to make it concrete and increase the performance .

Arunsiva05
  • 1