How to make null equal null in oracle

Question

I have a variable being passed to my stored proc and it's a filter (basically). However, that field can sometimes be null, and if it is, I want to be able to check against the rows that have that field as null.

For example,

Table A:

VALUE_COLUMN | FILTER_COLUMN
----------------------------
A            |  (NULL)
B            |  (NULL)           
C            |  (NULL)
D            |  (NULL)
A            |  1
E            |  (NULL)
F            |  (NULL)
B            |  1

The query (With inputs, val, filter):

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND brn_brand_id = filter

Expected I/O:

val = A, filter = (null) = 1
val = A, filter = 1 = 1
val = C, filter = 1 = 0

How can I make Oracle behave this way?

Malfist · Accepted Answer · 2011-08-08 18:03:13Z

9

How about:

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND ((brn_brand_id = filter) OR (brn_brand_id IS NULL AND filter IS NULL))

I'm not an Oracle expert, but I'd expect that to work - basically make the query match if both the filter and the value are NULL.

edited Aug 8, 2011 at 18:03

Malfist

31.9k64 gold badges187 silver badges272 bronze badges

answered Aug 8, 2011 at 17:58

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Malfist Over a year ago

I edited the sql to work correctly. But yes, this is what I was looking for, I don't know why I didn't think of it already.

Jon Skeet Over a year ago

@Malfist: Sorry, the previous syntax was for SQL Server. Shows you how long it is since I've done any SQL on either platform...

Malfist Over a year ago

I noticed, I'm used to do SQL Server also, doing all this Oracle stuff has thrown me for a loop.

Justin Cave · Accepted Answer · 2011-08-08 18:03:44Z

2

Oracle doesn't have an ISNULL function. So you'd need something like

SELECT COUNT(*)
  FROM tableA
 WHERE brn_brand_id = filter
    OR (    brn_brand_id IS NULL 
        AND filter IS NULL)

answered Aug 8, 2011 at 18:03

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Comments

Elyor Murodov · Accepted Answer · 2017-08-29 18:28:20Z

1

This can also be handy at times:

   SELECT COUNT(*)
      FROM tableA
   WHERE
      NVL(brn_brand_id, CHR(0)) = NVL(filter, CHR(0))

answered Aug 29, 2017 at 18:28

Elyor Murodov

1,02811 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to make null equal null in oracle

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related