I have two number columns: COST1 and COST2.

Values can be:

• Negative numbers
• Zero
• Positive numbers
• Null

with workorder
     (cost1,cost2) as (
select    1,    1 from dual union all
select -100, null from dual union all
select null,    0 from dual union all
select    0,  100 from dual union all
select null, null from dual
)

select
  *
from
  workorder

COST1	COST2
1	1
-100	null
null	0
0	100
null	null

db<>fiddle

---

I want to select rows where COST1 is different than COST2.

• I want to treat nulls as if they are equal.
• But I don't want to treat nulls as zeros.

Result:

COST1	COST2
-100	null
null	0
0	100

---

It would be equivalent to the is not syntax in SQLite:

with workorder 
(cost1,cost2) as (
values
(   1,    1),
(-100, null),
(null,    0),
(   0,  100),
(null, null)
)

select
  *
from
  workorder
where
  cost1 is not cost2

COST1	COST2
-100	null
null	0
0	100

db<>fiddle

---

How can I do that succinctly using Oracle SQL? (succinctness trumps performance)

CodePudding user response：

You can cast your values to strings, then transform your null values to "null" strings using the NVL function. Hence check the inequality.

where NVL(CAST(cost1 AS VARCHAR2(10)), 'null') <> NVL(CAST(cost2 AS VARCHAR2(10)), 'null')

---

Another option is combining your two conditions that check for null values with a single check: the concatenation of the two values should correspond to one or the other cost values.

where cost1 <> cost2
   OR CONCAT(cost1, cost2) IN (cost1, cost2)

Check the demos here.

CodePudding user response：

Here's one way. But it's not as succinct as I'd like:

with workorder
     (cost1,cost2) as (
select    1,    1 from dual union all
select -100, null from dual union all
select null,    0 from dual union all
select    0,  100 from dual union all
select null, null from dual
)

select
  *
from
  workorder
where
  cost1 <> cost2
  or (cost1 is null and cost2 is not null)
  or (cost1 is not null and cost2 is null)

COST1  COST2
-----  -----
 -100   null
 null      0
    0    100

db<>fiddle

CodePudding user response：

How about using binary_flaot_nan as the default for nvl?

with workorder (cost1,cost2) as (
    select    1,    1 from dual union all
    select -100, null from dual union all
    select null,    0 from dual union all
    select    0,  100 from dual union all
    select null, null from dual
)
select o.*
from   workorder o
where  nvl(cost1,binary_double_nan) <> nvl(cost2,binary_double_nan)