Dealing with NULLs in Oracle

Question

I have a query that is filtering using the :CONTRACT_TYPE. My contract_type contains both nulls and integers.

I cannot seem to deal with the nulls correctly, if I do the following, I return 0 results:

 WHERE CONTRACT_TYPE = NVL(:CONTRACT_TYPE, CONTRACT_TYPE)

I get the results I want when I simply do:

WHERE CONTRACT_TYPE is null

I want to be able to pass in the :CONTRACT_TYPE with both NULLS and integers.

Thanks all :)

Does this answer your question? [Oracle : sql select query if condition parameter is null then ignore the parameter](https://stackoverflow.com/questions/45881633/oracle-sql-select-query-if-condition-parameter-is-null-then-ignore-the-paramet) — Ori Marko, Aug 19 '20 at 10:19
The SQL standard uses `IS NOT DISTINCT FROM` for this, which Oracle doesn't support yet. So either use `AND`/`OR` to cover all cases (as shown by Sayan Malakshinov) or use `DECODE` (as shown by bjoern81). I often use `DECODE(contract_type, :contract_type, 'same', 'different') = 'same'` for readability. — Thorsten Kettner, Aug 19 '20 at 10:49

score 2 · Accepted Answer · edited Aug 19 '20 at 10:41

2

In order to get matching results for both nulls and non- nulls, you could try

WHERE decode(:CONTRACT_TYPE, CONTRACT_TYPE, 1) = 1

edited Aug 19 '20 at 10:41

David Buck

3,752
35
31
35

answered Aug 19 '20 at 10:35

bjoern81

36
3

I know its slighty off the topic of this question, but would you then know how to allow this to deal with multiple Ints within :CONTRACT_TYPE? So something like, NULL,1,2,3 being passed in? – Greg Aug 19 '20 at 11:40
1

@Fusiozii: As one bind variable contains only one value (Oracle doesn't have an array data type), you need one bind variable per parameter , e.g. `WHERE decode(:CONTRACT_TYPE1, CONTRACT_TYPE, 1) = 1 OR decode(:CONTRACT_TYPE2, CONTRACT_TYPE, 1) = 1`. Or you'd use one string and string funktions, e.g. `where '|' || :contract_type_string || '|' like '%|' || contract_type || '|%'`. – Thorsten Kettner Aug 19 '20 at 11:57

score 2 · Answer 2 · answered Aug 19 '20 at 10:38

Usually simple

WHERE 
   (:CONTRACT_TYPE is not null and CONTRACT_TYPE = :CONTRACT_TYPE)
 or 
   (:CONTRACT_TYPE is null and CONTRACT_TYPE is null)

works fine. But sometimes you may have a problems with suboptimimal executions plans when or_expansion/use_concat doesn't work. In such cases manual or-expansion using union all may help:

select ... from t where  (:CONTRACT_TYPE is not null and CONTRACT_TYPE = :CONTRACT_TYPE)
union all
select ... from t where  (:CONTRACT_TYPE is null and CONTRACT_TYPE is null)

Or there is also another approach - using partially documented function sys_op_map_nonnull: create function-based index on sys_op_map_nonnull(CONTRACT_TYPE) and use it in your predicates:

where sys_op_map_nonnull(CONTRACT_TYPE) = sys_op_map_nonnull(:CONTRACT_TYPE)

Dealing with NULLs in Oracle

2 Answers2