?
Solved

Help with Not Equal SQL Statement

Posted on 2007-10-17
9
Medium Priority
?
4,762 Views
Last Modified: 2008-01-09
I have the following code as part of one of my sql statements. I'm trying to account for all values in the field that are not equal to Yes, including Null values. For some reason, if the field has <NULL> in it, the statement doesn't work. If the field has 'No' or some other value besides 'Yes' it works fine. Do I need to change something to find Nulls as well? I was under the impression that <> 'Yes' would work. Please advise. Thanks!

......
AND [Has__bAPI__bMatch__bRun__Q]<> 'Yes'
0
Comment
Question by:isaacr25
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20094869
Change it to:
AND ( [Has__bAPI__bMatch__bRun__Q]<> 'Yes' OR Has__bAPI__bMatch__bRun__Q IS NULL)

Jim
0
 

Author Comment

by:isaacr25
ID: 20095110
I thought about that, but take a look at the entire statement:

UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null

Is there anyway to group the two parts to your suggestion so that its not interpreted as --all of these previous statments--- OR Has__bAPI__bMatch__bRun__Q IS NULL)?

Does this make sense? Thanks.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 20095116
>> I was under the impression that <> 'Yes' would work. Please advise. <<

SQL treats NULL as "Unknown" (essentially).  Futhermore, SQL's rule is that *any* comparison to an Unknown (NULL) yields Unknown (NULL).  That is the thing to keep in mind when dealing with NULL.

So, NULL <> 'Yes' yields NULL.  The clauses in a WHERE condition must all be *true* (not just "not false") in order for a row to be selected.  Since NULL/"Unknown" is not "true", SQL does not SELECT the row.

SQL also does not use any "extended logic" to handle things that would be obvious to humans.  For example:
WHERE ColA = 5 OR ColA <> 5
If ColA is NULL, it will *not* be selected, even though for any unknown value it must either be = 5 or <> 5.  SQL doesn't consider that (which, admittedly, could get very complex to do), but evaluates each expression separately, so WHERE Unknown OR Unknown yields Unknown (NULL), which is not true, so SQL doesn't SELECT the row.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Assisted Solution

by:mrihm
mrihm earned 200 total points
ID: 20095162
Another way to account for NULL values is to use the COALESCE function which returns the first non-null value in the supplied list:

AND COALESCE(Has__bAPI__bMatch__bRun__Q, '') <> 'Yes'

The Has__bAPI__bMatch__bRun__Q value will be replaced with an empty string if the Has__bAPI__bMatch__bRun__Q value equals NULL and you won't have any problems comparing it to 'Yes'.

The reason that your statement does not work with NULL is that the database engine does not know what NULL represents and does not make any assumptions (i.e. the database does not assume that NULL is not 'YES'). That being said, the database engine takes the most conservative rout and simply returns false when comparing a NULL value to 'Yes' and therefore any Has__bAPI__bMatch__bRun__Q with a NULL value will be excluded.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20095274
I am a bit confused. I do not see a relationship between this:
AND ( [Has__bAPI__bMatch__bRun__Q]<> 'Yes' OR Has__bAPI__bMatch__bRun__Q IS NULL)

and this:
UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null

Do you want to add that predicate to the join criteria? If so, to which table does Has__bAPI__bMatch__bRun__Q belong?

Jim
0
 

Author Comment

by:isaacr25
ID: 20095315
Hi Jim,
     Yes, I want to add the statement to the end of the larger statement. This field belongs to MASTER55.
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1600 total points
ID: 20095438
Like this?
UPDATE t1
  SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2.[Last Name]
 AND t1.mrREF_TO_MR Is null
AND ( t1.Has__bAPI__bMatch__bRun__Q <> 'Yes' OR t1.Has__bAPI__bMatch__bRun__Q IS NULL)

Jim
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 20095722
>> That being said, the database engine takes the most conservative rout and simply returns false when comparing a NULL value to 'Yes' <<

That is simply not true.  If it returned "False", then this would return "True":
WHERE NOT (columnA <> 'Yes')
when columnA is NULL, but it is not.  Instead, it also returns "NULL" (unknown), which is also not true for the purposes of a WHERE clause.
0
 

Author Comment

by:isaacr25
ID: 20095972
As simple as parentheses... imagine that!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question