CODEWITHSUNDEEP
sqloracle-database
Codash
Codash

Reputation: 99

Oracle Condition to Only Pull '0' Values

I need to pull ID's with only 0 values for both A and B columns. An example:


+----+------+------+
| ID |   A  |   B  |  
+----+------+------+
|  1 | null | 123  |  
|  2 | 23   | 768  |  
|  3 | 0    | 0    |  
|  4 | 96   | 0    |  
|  5 | 0    | null | 
|  6 | 0    | 0    |  
+----+------+------+

I have tried several queries, but I am still pulling through values above 0. As there are null values in the table, I have used the NVL(expr1,0) syntax to replace null with 0: 

+----+------+------+
| ID |   A  |   B  |  
+----+------+------+
|  1 |  0   | 123  |  
|  2 | 23   | 768  |  
|  3 | 0    | 0    |  
|  4 | 96   | 0    |  
|  5 | 0    | 0    | 
|  6 | 0    | 0    |  
+----+------+------+

I am using the following in my WHERE clause, and get the below results:


Where status = 'OPEN'
   AND a.value IS NULL OR a.value = '0'
   AND b.value IS NULL OR b.value = '0'

Output:

+----+----+-----+
| ID | A  |  B  |
+----+----+-----+
|  1 |  0 | 123 | 
|  3 |  0 |   0 |  
|  5 |  0 |   0 | 
|  6 |  0 |   0 | 
+----+----+-----+

It seems as though I am pulling only 0 values for A, but I am still getting values above 0 for B. I need to only pull ID's with a value of 0 for both A and B.

Upvotes: 0

Views: 51

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

I like Gordon's answer, but I would use COALESCE here for brevity:

SELECT *
...
WHERE
    status = 'OPEN' AND
    COALESCE(a.value, 0) = 0 AND
    COALESCE(b.value, 0) = 0;

We could also express using a sum:

WHERE
    status = 'OPEN' AND
    COALESCE(a.value, 0) + COALESCE(b.value, 0) = 0;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think you just need parentheses:

Where status = 'OPEN' AND
      (a.value IS NULL OR a.value = 0) AND
      (b.value IS NULL OR b.value = 0)

Upvotes: 1

Related Questions