CODEWITHSUNDEEP
oracle-database
will
will

Reputation: 897

How to write a query that looks for null in any field?

I'm trying to write a query in Oracle that will return the count of any row in the table with a null in any of the fields where they meet another parameter. Right now, I'm just using 

select count(*) from my_table where trunc(sent)>(to_date('01-APR-01', 'DD-Mon-YY')) and
field_1=null
or field_2=null
or field_3=null
etc.

Is there a better way to do this? And will combining "and" with "or" the way I have work the way I want it to at all?

Upvotes: 0

Views: 51

Answers (1)

Thilo
Thilo

Reputation: 262714

No. There may be a different way to phrase it (using COALESCE instead of OR for example), but you will have to explicitly enumerate all the columns no matter what you do, so it does not really make a difference.

More importantly, though, x = null does not work at all.

You need to use x IS NULL.

will combining "and" with "or" the way I have work the way I want it

When in doubt, use parentheses:

WHERE a AND (b OR c OR d)

Upvotes: 3

Related Questions