CODEWITHSUNDEEP
sqloracle-database
Eswar Vignesh
Eswar Vignesh

Reputation: 77

Filtering 0 or null values in two column

I have a output data as shown below

query used : select * from table 1

table 1 

name  a   b
pen   0   50
paper 10   0
bike   0   0
candy  0   0 
disk   20  30

the output should be 

name  a   b
pen   0   50
paper 10   0
disk   20  30

should eliminate the record with both a = 0 and b = 0

If I use the filter condition as 

select * 
from table1 
where a!=0 and b!=0

then I am getting only disk

Upvotes: 0

Views: 1268

Answers (3)

Thilo
Thilo

Reputation: 262860

You want

WHERE a != 0 OR b != 0  -- get records with at least one of [a, b]

If you happen to have NULL values in those columns they will be excluded just like 0 would (because NULL != 0 evaluates to false).

Upvotes: 3

Tedo G.
Tedo G.

Reputation: 1565

Try this one:

SELECT
    *
FROM
    table1
WHERE
    NOT (coalesce(a, 0) = 0 AND coalesce(b, 0) = 0)

Upvotes: 0

daZza
daZza

Reputation: 1689

The reason you are getting only disk with your where-clause is the AND you used. ANDmeans that both conditions must be true, so column a must not be 0 and column b must not 0.

Only when this condition is met, a result is shown. In your example only the disk dataset meets the condition.

To achieve what you want use an OR instead.

In order to filter NULL values you can check for IS NULL like this:

WHERE (a <> 0 AND a IS NOT NULL) OR (b <> 0 AND b IS NOT NULL)

Upvotes: 0

Related Questions