compare column name to either is null or 0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

compare column name to either is null or 0

Author

Topic

duncant
Starting Member

18 Posts

Posted - 2012-01-06 : 05:55:28

I'm trying to write a query unsuccessfully:

select * from tbl where FirstContact is not null and
CallComplete is NULL or CallComplete = 0
and DateAssignedToIO <> NULL

But I cannot get the CallComplete column to bring back results that are not null and of 0 values.

Can anyone help?

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 06:08:42

This may be because of precedence. Please try the below one,
select * from tbl where FirstContact is not null and
(CallComplete is NULL or CallComplete = 0)
and DateAssignedToIO <> NULL

Sachin.Nand

2937 Posts

Posted - 2012-01-06 : 06:08:49

select * from tbl where FirstContact is not null and
CallComplete is NULL or CallComplete = 0
and DateAssignedToIO IS NOT NULL

After Monday and Tuesday even the calendar says W T F ....

Kristen
Test

22859 Posts

Posted - 2012-01-06 : 06:11:03

If you are going to use AND and OR in the same WHERE clause it would be best to use parenthesise to indicate the precedence you want, otherwise you are relying on the the default precedence (and I can never remember which comes first! AND before OR I think ...)

You probably also need to change


DateAssignedToIO <> NULL
to
DateAssignedToIO IS NOT NULL

(unless you have some non-standard ANSII setting, but I don't recommend that)

So this maybe:


select *
from tbl
where FirstContact is not null and 
( CallComplete is NULL or CallComplete = 0 )
and DateAssignedToIO IS NOT NULL

but you might just as easily have meant something else, like:


select *
from tbl
where (FirstContact is not null and CallComplete is NULL )
      OR ( CallComplete = 0 and DateAssignedToIO IS NOT NULL )

which is what you will have got, by default, with AND precedence being higher than OR

and supply a column list for the SELECT, rather than using "SELECT *" - but I suspect you've just used SELECT * as an example here.

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 06:41:32

The below query may satisfy your requirement

select *
from tbl
where (FirstContact is not null and (CallComplete is NULL
OR CallComplete = 0) and DateAssignedToIO IS NOT NULL

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 06:43:07

The correct query is here,
select *
from tbl
where FirstContact is not null and (CallComplete is NULL
OR CallComplete = 0) and DateAssignedToIO IS NOT NULL

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 06:48:53

You can even simplify your query which is given below, if it matches your requirement.

select *
from tbl
where FirstContact is not null and IsNull(CallComplete,0) = 0 and DateAssignedToIO IS NOT NULL

duncant
Starting Member

18 Posts

Posted - 2012-01-06 : 07:08:09

Thank you to Sachin.Nand and Kirsten,

Changing
DateAssignedToIO <> NULL
to
DateAssignedToIO IS NOT NULL

resulted in the answer that I was looking for. SQL is a strange beast.

Kristen
Test

22859 Posts

Posted - 2012-01-06 : 08:30:34

quote:
Originally posted by duncant

SQL is a strange beast.

Well, not really, NOTHING is equal to NULL. Not even NULL equals itself. It is an "unknown" value. Thus you cannot say

XXX = NULL
or
XXX <> NULL

and instead have to use the specific-syntax

XXX IS NULL / XXX IS NOT NULL

to distinguish from the equality operators

(although that syntax is "supported" by setting ANSI_NULLS to OFF (which is non-standard)

P.S. You should put parenthesis too to make your AND / OR combination explicit. Even though Precedence may do what you want its a bug-waiting-to-happen when you/someone adds another AND or OR to the WHERE clause in the future etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources