Threshold comparison with NULL value

Question

We are building a report where some value is compared with thresholds. Logic is

if value > lower_threshold and value < upper_threshold then PASS else FAIL

However, for some of the parameters upper_thresholds are set as NULL. Which essentially means there is no upper threshold and if value <= lower_threshold then only it will FAIL otherwise it will always be PASS. We are building queries using Oracle SQL. Since, comparison of any value with NULL will always return FALSE, report is not working as expected. One option is:

if value > lower_threshold and value < nvl(upper_threshold, 9999999999999999) then PASS else FAIL

This is not a good way. Is there any other option to achieve the same?

Your post is tagged `sql`. Where are you using an **if** statement? There is no such thing in SQL. — , Aug 09 '19 at 16:00

score 5 · Accepted Answer · answered Aug 09 '19 at 15:57

or comes to mind:

if value > lower_threshold and (value < upper_threshold or upper_threshold is null) then PASS else FAIL

Of course, for an expression you would use case and for filtering in a where clause:

where value > lower_threshold and (value < upper_threshold or upper_threshold is null)

William Robertson · Answer 2 · 2019-08-10T09:10:38.293

Not exactly what you want because the lower range is inclusive, but perhaps you could consider the temporal validity syntax (requires 12.1 or later).

create table demo_ranges
( description      varchar2(20) not null
, lower_threshold  number unique
, upper_threshold  number unique
, check ( upper_threshold > lower_threshold ) );

alter table demo_ranges add period for threshold_range (lower_threshold, upper_threshold);

insert all
    into demo_ranges values ('Blue',   0,   10)
    into demo_ranges values ('Purple', 10,  20)
    into demo_ranges values ('Green',  20,  50)
    into demo_ranges values ('Yellow', 50,  100)
    into demo_ranges values ('Orange', 100, 200)
    into demo_ranges values ('Red',    200, null)
select null from dual;

Results:

select * from demo_ranges as of period for threshold_range 100;

DESCRIPTION          LOWER_THRESHOLD UPPER_THRESHOLD
-------------------- --------------- ---------------
Orange                           100             200

select * from demo_ranges as of period for threshold_range 1000;

DESCRIPTION          LOWER_THRESHOLD UPPER_THRESHOLD
-------------------- --------------- ---------------
Red                              200

Internally it builds the same SQL as in Gordon's answer (you can confirm this with dbms_utility.expand_sql_text).

Threshold comparison with NULL value

2 Answers2