Not Equal in where clause eliminating Nulls

Hello,

I have this query with a condition in where clause ActivityStatusId <> 100
but in the resultset it is eliminating nulls too.

How do I get around this one.

Thanks
rowmarkAsked:
Who is Participating?
 
xbradyCommented:
You can use something like this:
(ActivityStatusId IS NULL OR ActivityStatusId <> 100)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
NULL must be handled :
where ( ActivityStatusId <> 100 OR ActivityStatusId IS NULL )

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Try this:
SELECT *
FROM your_table_name
WHERE ISNULL(ActivityStatusId, 0) <> 100

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
where ActivityStatusId <> 100 or ActivityStatusId IS NULL
0
 
idlirCommented:
That is because a null value is undetermined, therefore it cannot be verified whether it's different from 100

If your condition is eliminating the null values, use the following

where coalesce(ActivityStatusId ,0) <> 100


This will return all the rows that have an ActivityStatusId with a predetermined value, and all those that are null (coalesce replaces null with 0 and 0 is always different from 100 )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
rowmark,

  glad you got your solution.
  note though that for fairness, if people post the same or alternative good solutions at the same time, you should really do a split instead of accept one of them!

angel eyes, zone advisor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.